Loop through excel spreadsheets in a workbook and process individually

[MarshaThompson]

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

[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.

[MarshaThompson · Accepted solution]

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