Macro Input Multiple excel files and sheets

[HunterH]

Hi Everyone. I have some pretty unstructured excel files that I have been able to re format with a workflow that works consistently across similar, yet slightly different excel files. I have set up a macro that will go in to a specified file, pull in file 1, re format it into my desired table format, then pull in file two, reformat, append to existing table, etc. over and over for every file in the specified location. 

My question is how I can make this process work for excel files with multiple sheets. Each sheet is an employee profile, and each manager has 1 excel file containing various sheets for their employees. Some files have 1 sheet, some have 15+ and anywhere in between. So far I have only been able to effectively grab the first sheet in each file.

Thanks for any suggestions provided.

[gabrielvilella]

If all the sheets have the same layout, you can use the dynamic input tool to list all the sheet names for every file, then just concatenate the sheet name to the full path as this: [FullPath]+"|||"+[SheetName].

If the sheets do not match the layout, you will need a batch macro to list the sheet names and another macro to read in the files. 

[mceleavey]

Hi @HunterH ,

I've built a couple of tools to do exactly this.

See attached.

The first tool reads in all sheet names from all workbooks as denoted in the Directory tool. These are then added to the fullpath (you can filter the ones you want). The second tool then loads them all in.

I hope this helps,

M.

[chickenlicken]

@mceleavey this is great