Loop through multiple excel files with different tab names

Question

Hi All -

Apologies for what is probably a basic question.

I have a directory with 20-30 excel files each with different sheet names and sheet numbers, but they are all numbered (i.e. 1.0 - Company Name1, 2.0 - Company Name1).

For the 1st sheet in each workbook (named 1.0 - Company Name) I need to cleanse the data (remove excess rows, and transpose columns, etc) and would then like to output the data to a single sheet for all the companies. The format of the 1st tab is the same for all Companies.

I would like to run this data cleansing for each file in the directory and then move on to the next file ending up with one output file with all companies.

I think I would need a combination of a batch macro to get the listing of file and sheet names, perform a filter to only grab the first sheet, and then an iterative macro to run the data cleansing.

I am running into a little bit of trouble of how I would format the input tools in this scenario

Any help would be appreciated.

Tim

cmcclellan · Accepted Answer

The Crew Macro Pack might help - https://community.alteryx.com/t5/Engine-Works-Blog/Crew-Macro-Pack-2016-Q2-Release/ba-p/26482

There is one called Wildcard XLSX input that will do what you describe, although you might have to play with the output a bit.