VBA: Combine Excel files from multiple folders into one worksheet based on having the same 4 first characters in the file name.xlsx
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:
Folder 2 will have:
Folder 3 will have:
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.
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!
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.
To merge basic files before I was 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.
This example/VBA is good if all the files are on the same folder: It would be great to add the different folder paths and based it off the names containing the customer ID ###.
Sub ConslidateWorkbooks() 'Created by Sumit Bansal from https://trumpexcel.com Dim FolderPath As String Dim Filename As String Dim Sheet As Worksheet Application.ScreenUpdating = False FolderPath = Environ("userprofile") & "\Desktop\Test\" Filename = Dir(FolderPath & "*.xls*") Do While Filename <> "" Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet Workbooks(Filename).Close Filename = Dir() Loop Application.ScreenUpdating = True End Sub