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.
Can you send me this macro too?
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 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.
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.