Combine Multi-sheet Excel Files

 

Problem description

There are several Excel files in a folder. All the files contain the same number of sheets that have same names. Namsake sheets have same number of columns and the columns have same names. Below are sheets in workbook1.xlsx:

undefined


undefined

The task is to combine data in all files into a single file output.xlsx, where data in namesake sheets is stored in a sheet under the original name that uses column headers in the first file’s sheet and receives detailed data rows in all sheets. Below shows the combined data in the result sheet1:

undefined

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

 

Directions

1. Write a script:

A

B

C

D

1

>dir="E:/excels/"

2

=directory(dir+"*.xlsx")

3

for A2

=file(dir+A3).xlsopen()

4

if #A3==1

>C2=B3

next

5

for C2

=B3.xlsimport@t(;B5.stname)

6

=C2.xlsexport@a(C5;B5.stname)

7

=file(dir+"output.xlsx").xlswrite(C2)

A1   Define variable dir of the folder containing the target Excel file.

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-open Excel object to C2 and go on to handle the next file.

B5   Loop through each sheet in C2.

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

C6   Append data read by C5 to the current sheet in C2; @a 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