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.
Solved! Go to Solution.
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
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
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.
Thanks @grazitti_sapna & @RolandSchubert for your solutions. Really helped me to go ahead with my workflow.
Could you please give more explanation on certain areas of your workflow which i am not so clear about?
Please see attached screenshots from your workflow where i have highlighted my areas of doubt.
Appreciate your time.