Import multiple TXT files to one Excel csv sheet using Text Import Wizard macro?

Question

I need to import data from approximately 30 individual TXT files into one CSV sheet. They are all formatted the same, so the Text Import Wizard should work. However, instead of running the Text Import Wizard 30 times, I would like to create a macro that will automatically run 30 times for each individual txt file in a single folder.

I am able to create a macro for a specific file, but I need help creating a macro that will run for each of the 30 txt files (unique file names) and then import the data into the next empty cell after the previous file import.

Answer 1

This is a job for Power Query. That's what it was invented for. It's available for all the last ten years' versions of Excel, starting with free add-ins for XL 2010 and XL 2013 and built into the application since XL 2016, named "Get & Transform".

Use the "From Folder" option in the data ribbon, then in the Power Query editor, remove the files you don't need (if any) by using filters. Then expand the remaining files.

All this can be done without writing a single line of code, just by clicking on ribbon icons.

And the saved query can be refreshed and repeated at any time if there is new data in the source folder.