Hello, I'm learning Access right now and it's pretty empowering! The Macro creation system is easy but I need some help to make this work.
I have 5 to 10 Excel .xlsx files in a single folder.
Each Excel file has a Table on the first sheet, with the same headers.
I need to combine these tables into one "Master" table which creates a new "Master" Excel file.
So far, I have only been able to create an Excel Macro which uses multiple external connections and copy/paste, but I want to utilize Access's power to make it a one-click shortcut that anyone could do.
I want the new method to work regardless of how many Excel files are in the source folder.
I need to keep all rows, and all duplicates, all tables. I will combine them based on their left-most column A which is labeled [ID Number].
The reason is because each source file is worked on by a different person. They may have the same [ID Number] in their files by accident. So as a feature, I need to spot this and remove it by consolidating their files.
I know that Access can do multiple outer-joins (and basic Excel VBA needs extensive programming to do so) but I can't develop a macro to work the way I need it to. This needs to be a simple one-click solution.
Sadly, I cannot use "Power Query" for Excel because I am restricted to Internet Explorer 8. I have no option to upgrade to IE 9 or above.
Thank you! I appreciate all input.