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

[binu_acs]

@MDonnel8 One way of doing this with the batch macro

[MDonnel8]

Thanks for your reply! How exactly do I set up the batch macro to suit my needs?

[binu_acs]

@MDonnel8 The attached macros should work for you, you need to change the path of the directory tool. Let me know if you face any issues for running the workflow 

[MDonnel8]

I changed the directory to point towards where my files are stored and received the following output: 

The output only gives one sheet and none of these columns appear in my files.

[binu_acs]

@MDonnel8 your input files are .xls files? What is the output from the directory tool?

[MDonnel8]

Yes they are, is that an issue?

Below is the output from the directory tool:

[binu_acs]

@MDonnel8 one question, are you  able to run the attached workflow without any change and getting the same result mentioned in the image which I provided , the macro looking for .xlsx files, right now I’m away from my system, I will test with .xls files once I’m back. Would you mind attach some .xls file with dummy data?

[MDonnel8]

Yes the only change I made to your workflow was to the directory and this gave me the output I screenshot above.

I've attached an example xls file - if this proves to be an issue I can change the files to xlsx.

[MDonnel8]

@binu_acs have you gotten a chance to look at this since? This is something I would like to be able to do today - really appreciate your help so far by the way!