Running a Batch Macro on Multiple Tabs in the Same Excel File

Hello,

Is it possible to run a batch macro on multiple tabs of the same Excel file? I attached a workflow that I'm working on with the macro that I'd like to run on all of the tabs individually in the Sample Input. Could I get some guidance configuring the control parameter of the macro so that the macro will run on all of these tabs and produce an output similar to the target output file I also attached?

Thanks so much!!

Sample Input.xlsx

Sample Workflow.yxzp

Target Output.xlsx

Batch Macro

Accepted answers

jrgo

@chester50

You were almost there. The main issue to why your macro wasn't working is because your Control Parameter configured to update the input tool with the field you selected, which you had just the sheet name going in.

Using a formula tool, created a new field to contain the full file path, including the sheet name, and used that field for the control param.

I also made some modifications to your macro so that it's able to properly parse out the sheet name.

Reach out if there's any questions to the changes i made, but hopefully this helps!

Jimmy
Teknion Data Solutions

Sample Final Workflow_edit.yxzp

All comments

ydmuley

Hello @chester50 - Why not first pulling in all the data from multiple tabs and later using the macro to achieve the desired outcome.

chester50

Hi @ydmuley - thanks for the suggestion. When creating the sample data used in the workflow I uploaded, I really simplified it just for demonstration purposes. In my real input file, each entity tab will not have a consistent number of state fields, as our export only populates columns for tabs that have activity in each state. So when I try to use the dynamic input approach, the dynamic input tool skips tabs with the error message "The file XXX has a different number of fields than the 1st file and will be skipped", thus meaning that many of the tabs do not make it through the dynamic input. That's why I was attempting to use the batch macro approach - do you know if that would be possible?

mmishal001

Refer to answers in this post

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Duplicating-one-workflow-for-multiple-sheets-of-the-same-format/m-p/478545/highlight/false#M96072

jrgo

@chester50

Using a formula tool, created a new field to contain the full file path, including the sheet name, and used that field for the control param.

I also made some modifications to your macro so that it's able to properly parse out the sheet name.

Reach out if there's any questions to the changes i made, but hopefully this helps!

Jimmy
Teknion Data Solutions

Sample Final Workflow_edit.yxzp

itahir04

Hi Jimmy,

The question is how to construct the batch macro to read multiple tab. In you rworkflow, you are using a batch macro which is already constructed, saved, and then used.

Can you please send me the configuaration part of the batch macro.

I have a fle with multiple tabs, I would like to run workflow on each tab and union the data to get a master data file.

Thanks,

Rana

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335