Batch Input Macro help

[mb1824]

Hi,

I am trying to modify the Macro from this post for my own use case https://community.alteryx.com/t5/Alteryx-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480

When I run my workflow, I get the below error.

I can get around the error by doing the following but the files in the link don't include this modification so what am I doing wrong?

1. Open the Macro
2. Click on the "Action" tool, enable "Replace a specific string" and remove the Sheet name from the box below.

The files in the Link use the field [FullPath] as the parameter for the Macro, which doesn't include the sheet name. This is the same in my Use case.

[Matt_Curtis]

I think you'll want to enable 'Replace a specific string' and delete everything BUT the sheet name.  In this case actually you'll probably just want to leave the text:  Sheet1  in that text box on the bottom.  Excel needs the '$' characters to finish the syntax, in this case you're simply replacing the name only.

Edit:  should've read the whole post, sorry.  You either have to use the full path from the Directory tool and replace the whole thing (Click on the 'File - Value....' line and enable Replace a specific string) or you can re-use the path and/or filename and just swap the sheet names out (using the method I first described).

Also, since you downloaded a packaged workbook, you're working off of a weird /externals file path that is common in downloaded workflows.  Save your 'CO Store File' somewhere local in a fresh folder and it'll be easier to diagnose.

[mb1824]

Thanks for the comments.

My use case is the same as the files in the link in that I have multiple workbooks with different filenames and I want to input the same tab from each workbook.

Therefore I need to replace the filename not the sheet name.

But regardless, why don't the files in the link have one of the below:

1. 'Replace a specific string' enabled, or
2. The sheet name included within the field [FullPath]

[danilang · Accepted solution]

@mb1824

The batch macro from the example doesn't require additional options because the sample files only have one sheet.  As long as there is only one sheet per file, the macro runs fine.  The sheet names names don't have to match the template.  The naming doesn't have to be consistent across the the files.  You can have 2 with "sheet1" and one with "Stores".  As soon as you add another sheet to any of the files though, the macro breaks.  It seems that the Input tool will load any excel file assuming there is only only sheet in it.  I actually went into the xml of the macro and removed the Table or Query string so the config of the input tool now looks like this

And it still works properly if the workbooks only have one sheet each

So the sample macro is configured to work with the sample data.  Change the sample data too much and the macro no longer works

It's like giving a demo of a beta product to the CEO.  Never deviate from the script

Dan

[mb1824]

Thanks @danilang

My workbooks have multiple tabs so that explains why I needed the additional options