VBA: Combine Excel files from multiple folders into one worksheet based on having the same 4 first characters in the file name.xlsx
Description:
There are files with a customer ID from 001-100. Each customer has 3 different Excel reports (### Report1-3), which are generated into different folders (Folders1-3).
For example: Folder 1 will have:
001 Report1.xlsx
…
100 Report1.xlsx
Folder 2 will have:
001 Report2.xlsx
…
100 Report2.xlsx
Folder 3 will have:
001 Report3.xlsx
…
100 Report3.xlsx
Each of these files contains only one sheet with Values (no formulas nor PivotTables). Each Report 1, Report 2, Report 3 have a different set of columns/rows.
Question:
I would like to create one workbook for each Customer ID “### ” and have the reports as Sheets (### Report1) (### Report 2) (### Report 3) (All the desired documents to be combined into one workbook will have the first four characters at the beginning of each report’s name)
For example for Customer: 001
Create one “001 AllReports.xlsx” workbook that has:
Sheet 1 = 001 Report1
Sheet 2 = 001 Report2
Sheet 3 = 001 Report3
Then move to Customer ID 002 and do the same.
Please let me know if anything else needs to be clarified. Thanks, I truly appreciate it!
Some notes/thoughts:
I was thinking of creating a Merger Macro template that has 3 fields. Where I fill in Folder 1, then Folder 2, and Folder 3 paths and then proceed to merge the documents based on the Customer ID.
I have only merged basic file before based of:
https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.copy
I was also curious about doing it with a query but since the columns are not consistent across Report1-3 I am not sure if it would be even an option.