0

I have got the following Problem. I have several Excel files containing each the data of a country in one folder.

However I want to pull that all into one Excel report.

As the content of the source files change dayly, I guess the best way to do that is to do a import via an SQL Statement using Union All.

However the problem is that MSQuery only allows me to Access one file at a time. Is there a Workaround for that problem?

Maybe create a data model and use DAX?

whytheq
  • 34,466
  • 65
  • 172
  • 267
Richard Wonner
  • 35
  • 5
  • Can you share some examples? I can visualize 2 approaches. One is to link an Access table to each Excel file, then use UNION to bring them all together. The other is to use VB script to loop through all the Excel files and import them to a common table, appending each file to the table. – Don George May 02 '16 at 12:47
  • 1
    I'm unsure that mdx is a relevant tag. Data model sounds more like tabular/dax related? – whytheq May 02 '16 at 17:41

1 Answers1

1

This sounds like a job for Power Query, a free add-in from Microsoft for Excel 2010 and Excel 2013, and built into Excel 2016 as "Get and Transform" in the Data ribbon.

You can create individual queries to the different Excel files in the different folder, then create a query that appends all previous queries into one table, which can be loaded to the Excel data model or a worksheet table for further processing.

The queries can be refreshed with a click when the data has changed.

teylyn
  • 34,374
  • 4
  • 53
  • 73