Import Excel File with Multiple Sheets and Union with Other Excel Files with similar Sheet

[MDonnel8]

Hi all,

I have approx. 40 excel files (in the same directory) each with 20 sheets in them. Each file has the same number of sheets, the same sheet names and each corresponding sheet name has the same format (i.e. sheet 1 has the same format in all files, sheet 2 has the same format in all files, etc. although sheet 1 and sheet 2 have different schema). 

I would like to union all the files by sheet name so that I can output one large file with all 20 sheets containing data from the 40 files. Is there a way I can do this? Any help would be appreciated!

Kind regards,

Michael

[binuacs]

@MDonnel8 .xls file will not work with the above macros. you have the options to change from .xls to .xlsx then that would be the simple way to solve the issues. please go through the below post regarding how to process .xls files

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Input-tool-use-of-xls-file-ext...

[binuacs · Accepted solution]

@MDonnel8 I updated the input file from .xls to .xlsx and generated the output using the same workflow

[MDonnel8]

This works thank you!