Problem description

There are several Excel files in a folder. The number and name of sheets in all files are the same, and the number and name of columns in sheets with the same name are the same as well. Below are sheets in workbook1.xlsx:

The task is to combine data in all files into a single file output.xlsx, where data in sheets of the same name will be stored in a sheet of the original name where only the column headers in the first file’s sheet are saved and rows of other files are just merged. Below shows the combined data in the result sheet1: 

The computing task involves listing files under a specific folder, importing sheet data from Excel files and appending data to sheets. 

Directions

1. Write a script:

A1   Define variable dir to store the folder where the target Excel file will locate.

A2   List names of all xlsx files in folder dir.

A3   Loop through each file in A2.

B3   Open the current file in A3 as an Excel object.

B4-D4   If the current file within loop is the fist file, store the currently-opened Excel object to C2 and go on to the next file.

B5   Loop through each sheet in C2.

C5   Import the data of the current sheet name from B3’s Excel object; @t option enables reading the first row as column headers.

C6   Append data read in C5 to the current sheet in C2; @a option enables data appending.

A7   Write the combined C2 to output.xlsx.

2. Run the program and then we can view the combined data in output.xlsx in dir folder.

Q & A Collection  

https://stackoverflow.com/questions/63944588/vba-combine-multiple-workbookswith-mutiple-worksheets-into-one-workbook-with

【Attachment】mergeexcel.zip