Excel Power Query #06: Import Multiple Excel Files From Folder Into One Excel Table or Data Model
Why Stack Overflow recommends it
Stack Overflow users link it when someone needs to combine many Excel files with the same layout and Power Query is a better route than macros or manual copying.
Recommended by 3 people across 3 different months, 2015 to 2017.
What it is
A short tutorial from excelisfun showing how to use Excel's Power Query to import every workbook in a folder into one Excel table or the Data Model. It builds a PivotTable with a slicer, then drops new files into the folder and refreshes to show the new data flows through.
What people said
Recommends rwilson · 2015-07 · Stack Overflow · on “Emailed Excel Spreadsheet to Multiple CSV Files”
Once you find a way to automate Outlook and pull your Excel attachment effectively, you definitely want to look into Power Query (A free Addin for Excel 2010/2013 and built into Excel 2016). Power Query gives you an easy way to establish connections to different data sources, in which you can then transform the data (Insert Columns, perform aggregations, etc) and then load it into Excel or Power Pivot if you choose to go down that route. Once you perform the task once, it will automate all your steps when you refresh. For example, you can set up a connection to a folder that will append all Excel files in the folder to a master file. So each time an Excel file gets dropped into that folder,
Recommends Slai · 2016-08 · Stack Overflow · on “Making excel macro for file scanning more stable”
For Excel 2010 or above I recommend looking into Power Query [this video] . Other ways to read from Excel files without opening them are ADODB [LINK] and External references [LINK]
Recommends RedVII · 2017-01 · Stack Overflow · on “Consolidating multiple tables from different workbooks with same fields into one table using PowerPivot”
Your solution is with Power Query. You simply have to go to Data tab -> New Query -> From File -> From Folder, and select the folder which contains all your excel files. From there, click on Edit and this will bring you to the Power Query interface, where you can join all the files together. This video [this video] is an excellent guide to achieving this. If done right you should have them all combined in minutes. Once everything is combined in Power Query, you can easily load the data into Power Pivot.