Batch Macro - Appending Column from SheetName

[Cbennett022]

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.

[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

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

[fmvizcaino]

Hi @Cbennett022 ,

You need to get the sheetnames before entering the macro.

So the sequence would be a directory tool to get all the file names, then to use a sheet finder tool to get all sheets from all files, a formula tool to build the filename+sheetname and then connect to your macro.

The only tool you need to insert in your workflow is the sheet finder.

I'm attaching an example for you to check it out.

Best,

Fernando Vizcaino

[DavidP · Accepted solution]

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.

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

[Cbennett022]

This worked perfectly, thank you for all of the help. It is greatly appreciated.