Batch Macro - Appending Column from SheetName

I am attempting to create a batch macro that reads a number of sheets from a single excel file. They have the same schema. The idea is to group by the company code, account code and name columns. The values from each sheet would then become the columns to the right of the grouped fields with the sheetname being the column header.

As an example, using the data attached, company ABC would be listed in column A followed by the account code and name in columns B & C. The subsequent columns D and onward would be the sheet names i.e. TAR PT Change, TAR PT ACQ and so on.

I am getting an error that reads sheet not specified when using the macro in my workflow. Any help is appreciated. Please see screenshots below for reference.

Alteryx Dummy Data.xlsx

Dummy Data WF 1.24.yxzp

Batch Macro

Accepted answers

DavidP

I'm not sure you need a macro here. I can't exactly figure out your logic, but all the sheets (with the range that contains the data and the sheetnames can quite easily be loaded with dynamic input tools. See below. Can you get from here to what you need with a Crosstab maybe?

With the attached workflow file, just open it in a text editor and change the version number in the 1st line to your version and then you should be able to open it.

New Workflow11.yxmd

All comments

fmvizcaino

Hi @Cbennett022 ,

The problem is that you need to provide the sheet name alongside the file name. As you can see in the action tool, the sheet name is also something you need to pass as a parameter.

I'm attaching an example where I'm using a macro called sheet finder to map all sheets inside a excel file.

LEt me know if this makes sense to you.

Best,

Fernando Vizcaino

Ex4.zip

Cbennett022

Thanks for the response!

I have an older version and cannot open the workflow, unfortunately.

However, from inside the macro, if I choose a list of sheet names instead of the specific tab, how am I able to apply the rest of the workflow to each sheet and combine the columns at the end?

DavidP

With the attached workflow file, just open it in a text editor and change the version number in the 1st line to your version and then you should be able to open it.

New Workflow11.yxmd

DavidP

Is this what you're looking for? I amended my 1st attempt a little to bring in the last bit of logic from your macro. One of the things I did was to specify the cell range of the data to be read from the excel files.

See workflow attached.

New Workflow11.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703