Batch macro that allows me to read multiple files with multiple sheets.

Question

Good Afternoon,

I have been researching everywhere to get help with batch macros. I have found many awesome solutions with batch macros, all that are related to either using a macro to read 1 excel file with multiple sheets inside of it, or multiple excel files with only 1 sheet inside. My situation is a little different. I need a batch macro to read multiple files that have multiple sheets inside of each. The number of sheets range from 1-6 in each file, and the schema is different., but the data should be able to be mapped by name or position (I have been trying to get it to work) If a certain tab exists in one sheet, but not in the other, to just not run the certain tab for that certain file. I am not sure how to do this, and am reaching out for help.

Snip2.PNG

Snip1PNG.PNG

philipmannering · Answer

Hey @shaikhrida7 could you share a couple of examples (obfuscated/with dummy data if required)

shaikhrida7 · Answer

I would like all sheets that have Income Stmt, or Balance Sheet in the name of the sheets from all of the xlsx files. For example in one of the files there is only Income Stmt and Balance sheet in one file. Then the next has Income Stmt, Income Stmt Grs. Contributions, Balance Sheet Grs. Contributions. So as long as it contains Income Stmt, or balance sheet, I would like it to union those- tabs and make it all 1 big table. Hopefully that makes sense. Thank you for such a quick reply.

philipmannering · Answer

Hey @shaikhrida7

So just to clarify, you want all sheets from all xlsx files. Or you want only the sheets that exist in all of the xlsx files?