Combining multiple excels into one workbook with multiple tabs.

I have 20 excel workbooks in a single folder. The name of each excel is in the same format with a specific identifier. Now i need to combine all the excels into one workbook with 20 tabs. All the 20 files has single tab and the name of the tab is same across all these files. Also, i need to rename the tabs in the final output using the specific identifier in the file name. Been reading about directory input and dynamic input; can't seem to get those solutions to work. Can someone help me with a solution for this.

Accepted answers

RolandSchubert

Hi @Anjitha3913 ,

is the structure of all sheets identical(e.g. same columns , column headers)? You can use the Directory tool, Dynamic Input tool and Output Data tool, but will need a few formulas to create the right names. I've attached a sample workflow and test files.

Let me know if it works for you.

Best,

Roland

Files.zip

ReadFiles.yxmd

grazitti_sapna

Hi @Anjitha3913,

If your files are having different schemas then please set the macro to Auto Configure by Name or position in the Interface Designer, then it won't error out.

NOTE: Sheets within the same files should have the same schema.

I hope this helps.

All comments

RolandSchubert

Hi @Anjitha3913 ,

Let me know if it works for you.

Best,

Roland

Files.zip

ReadFiles.yxmd

Anjitha3913

Hi @RolandSchubert

Thanks for your help on my query. I saw your result and this is exactly what i require. But do you have a solution in case my files have different schema, as its difficult to identify what is exactly different when the error on different schema pops up. I also tried a batch macro reading another article (i am very new to macros).

Error without using batch macro : "fullpath///sheet name" has a different schema than the 1st file in the set.

Error when using batch macro : Record #: Tool #5: The field schema for the output "Output 5" changed between iterations.

Out of my 20 inputs except for 2 files, these errors are poping up.

Regards,

Anjitha

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703