Loop through excel spreadsheets in a workbook and process individually

Help!....

Here's the problem. I have a Excel workbook with mulitple tabs (one per GL Code). Each tab has the same layout and each tab requires its own report.

example: GL Code 301 on the first tab would create report 301

GL Code 302 on the second tab would create report 302

etc.

I know how to get the names of the Excel spreadsheets as output. Using the outputted list of spreadsheet names, dynamically input the data and run through the logic. I'm thinking that I'll need to create an interactive marco but I'm not sure how to handle the actual input of the data (dynamically) in that macro

Can some one get me started?

Thanks

Workflow

Input

Iterative Macro

Accepted answers

MarshaThompson

Thanks so much this is definitely going to get me started.....now to write the main flow!

All comments

rarmstrong

Use input tool to input your file.

In the 'Table or Query' field, select <List of Sheet Names>

In the 'Output File Name as Field', select Full Path.

Your results will include the list of sheet names and the file path.

Once you have the file path and the sheet names then you can use the formula tool to make your new file path that includes the sheet names. Formula is: Replace([FileName],"<List of Sheet Names>",[Sheet Names])

Then use the dynamic input tool to input all sheets, making sure to select output full path again. Now that you have the sheet name in your report, you can run through your workflow as usual and then create your separate reports based on the field that includes the sheet name.

Sheet Names_3.PNG Sheet Names.PNG

MarshaThompson

Thanks so much this is definitely going to get me started.....now to write the main flow!

Humza_Ahmed

I was looking for something similar earlier - thanks for sharing

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703