How can I auto populate several excel sheets from other Excel files

I am currently working on a power bi dashboard that uses an excel file as a data source. I want to auto populate the excel file with new values from existing excel reports each day.

In the source file there are several sheets each with several columns (date, inventory, capacity of transportation, etc.)

My objective is to get specific column values from each excel report corresponding to today's date and add them to the corresponding columns in the source file (while adding a new row corresponding to today's date).

I have checked several articles on how to use Microsoft Azure and VBA but I didn't find exactly what I am looking for. Especially that the reports need some cleaning since they contain headers, titles, comments, etc.

Any ideas?

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum