Retain sheet name when using a batch macro input

[Kearnd967]

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.

[Kearnd967]

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

[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

[DataNath · Accepted solution]

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.

[Kearnd967]

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