Building Macro to go tab by tab and THEN file by file in a Directory folder

[shelbybradley]

Hello! 

I am looking to build a macro workflow that can:

1. rerun the same workflow for a series of tabs within the same Excel document

2. consolidates all outputs for each Excel document (which contains multiple data tabs)

For context, in the past, I have used the Control Parameter, Action, and Macro Output tools to build a macro workflow. And then I used the Directory tool to run through every file in that directory folder. What makes this situation different is that in the past, I only needed to run the analysis on (1) tab within each excel document, and repeat for each file. This time, I need the macro to go tab by tab for (16) out of the (20) tabs in each excel document, and then go file by file to repeat the process. 

My question is, how do I alter the setting of the macro tools in order to configure a workflow to go (a) tab by tab and then (b) file by file. In the past, I have only done file by file. 

If you need an example data set to use, please use the attached.

Any help is much appreciated!

Thank you

[tristank]

HI @shelbybradley 

Something like this solution here

That is a way to generate all of the excel sheets within an excel file. From there you could run them through using a batch macro (the control action would be on the input tool). You would then likely need another batch macro to go file by file. Overall sounds like a batch macro within a batch macro.

Here is another video on reading excel sheets into Alteryx

Hope this is a helpful start :)

Tristan

[binu_acs]

@shelbybradley The attached workflow should work for your use-case. The first batch macro reads all the sheet names and the second macro combined all the sheets into one