Retain sheet name when using a batch macro input

I want to use a batch macro to bring in multiple sheets from the same excel file. They are all structured the same but have different sheet names. I would like to retain the sheet name in the joined data.

I am really struggling with this one, any help would be greatly appreciated.

David.

Input

Batch Macro

Accepted answers

DataNath

Ah @Kearnd967 I can see the problem now. At the minute you're bringing in a single sheet, getting the full FilePath of that and then passing that to your batch macro, so the batch macro once again just brings in this single sheet, hence why you're only seeing that come out.

What you need to do is:

1) From the Input Data tool in your main/outside workflow, under 'Table or Query', select 'Import only the list of sheet names'

2) Pass this list of sheet names to the control parameter

3) In your action tool in the macro, tick the 'Replace a specific string' option and reduce the text in the box below so it's only the sheet name i.e. CL0002 - nothing else

Now when your batch macro runs, it'll run an iteration for each sheet, replacing the final (sheet) part of the filepath each time.

All comments

DataNath

Hey @Kearnd967, in the Input Data tool that sits inside your macro, if you go to the 5th option (Output File Name as Field) and select 'Full Path' - this will output the entire filepath into the data when the macro has finished running, including the sheet name at the end. From here, you can either keep it as is or parse it out, depending on the format you want.

For parsing the sheet name, you can just stick a RegEx tool on in Parse mode, select the [FileName] field and use something like the following as the expression:

`([^$]+)

Hope this helps!

Kearnd967

I think I have done that already. See attached. Not sure if its because I am trying to import from a specific row?

2023-09-21_14h10_11.png

DataNath

@Kearnd967 the image you attached shows the configuration of your Action tool. I mean the configuration of your actual Input Data tool - if you just change that 5th option to output the Full Path then you should see that come through into the data when you run the macro.

Kearnd967

Sorry, I dragged in the wrong pic. This is the input tool and action tool side by side. This shows Option 5 already selected. Perhaps I am doing something wrong on the main workflow

2023-09-21_14h09_59.png

DataNath

Hey @Kearnd967 I notice the name of your macro is still starred i.e. unsaved. Since ticking Option 5, have you re-saved it? That option is all you need to see the full FilePath so should be working otherwise.

OllieClarke

@Kearnd967 can you show the workflow and the data going into the batch macro please?

Kearnd967

This is all I have at the moment. I am quite new to macros.

2023-09-21_14h27_15.png

2023-09-21_14h27_31.png

Kearnd967

Thank you. It works in the macros, but doesnt work properly in the workflow. It brings through all sheets with the same sheet name.

OllieClarke

@Kearnd967 you'd need to have the fullpath of the file and the sheetnames in it entering your control parameter for your macro.

If these sheets are all in the same excel, and have the same schema, then you could use the dynamic input tool as I've done in the attached workflow

sheetnames.yxzp

DataNath

What you need to do is:

1) From the Input Data tool in your main/outside workflow, under 'Table or Query', select 'Import only the list of sheet names'

2) Pass this list of sheet names to the control parameter

3) In your action tool in the macro, tick the 'Replace a specific string' option and reduce the text in the box below so it's only the sheet name i.e. CL0002 - nothing else

Now when your batch macro runs, it'll run an iteration for each sheet, replacing the final (sheet) part of the filepath each time.

Kearnd967

Thank you both so much. I now see the error and it is working as expected.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703