Macro Input Multiple excel files and sheets

Question

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.

alitovch · Answer

This is great. Can you provide insights please how you built this?

chickenlicken · Answer

@mceleavey this is great

mceleavey · Answer

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.

Multi Sheet Import Example.yxzp