Combine multiple sheets in an Excel file with different header and order

[Beth630]

Hi,

I just get a task to combine some sheets in one Excel file, and the sheets names are the dates ( 07012021, 07022021... ), but the headers and order of the sheets come out different, so when I use an input tool to get the sheetnames and link a macro input tool to read all the sheets, get the error message Error: Dynamic Input (20): The file "...\07022021" has a different schema than the 1st file in the set.

If use Excel only I will find and replace the "OrderNum" to "Order", or "Amount" to "Price", but I cannot find a better way to reorder the columns or rename the fieldnames automatically. 

Order

Customer

State

Amount

Address

ID

Amount

OrderNum

Order

State

Price

Customer

Price

Order

StateName

CustomerID

And I tried to design a macro to rename the header, split the data and header, transpose the header part and Find & Replace to match the fieldname, then Union the header and data. But how to use this before read the sheets is a problem. 

[dougperez]

Can you send me this macro too? 

[JoaoLeiteV]

Hey there @Beth630,

Try checking out this tutorial. I think this can help you out.

The best solution is reading all files in a folder to get their path, then use a Macro to read each excel and get their sheet names, then another macro inside the first one that receives a fullpath + sheetname and read each individual sheet, appending the results by name.

[dougperez]

Hello!

I created this to do the work...

[Beth630]

@dougperez Here is my Macro to fix the header, I would like to read each sheet into this macro and replace the header into the same words, and then join them with name, to generate a full worksheet with each record, but still not work. And your solution is super cool to solve it, that's really something that I never tried, Thanks.

[Beth630]

@ JoaoLeiteV

Thanks for your reply. Yep, that's is a good solution, although I tried to solve it by macro but my problem is ...I didn't use 2 embedded macros. The original design will append all the columns to one table like this:

But it will be easy to combine the columns. Just use Formula tool and set some conditions we can join the data to correct columns.

And the result:

Thanks again for Andrew's solution and your share.