Dynamically select which excel file's macro to run

[vj28]

Generally if I want to run a specific excel file's macro through Alteryx, I just use run tool with wscript and a .vbs file (that invokes said excel file) as inputs. That vbs file obviously has the name of the excel file whos macro needs to run hard-coded. 

But now I'm in a situation where I need to access a folder where new excel files get added all the time; I need to access the latest file (i do this using the sort tool) and run a macro called "populate" within that file all through Alteryx. All the files in that folder have the macro named "populate", but I only need to run the one in the latest file that I picked. 

Is there a way to do this within Alteryx? 

[KGT]

On the Run command, you can write out a vbs script (build it in the workflow beforehand). This is the output option at the top of the Run Command interface.

See in this example how they write the stream out to a csv: https://knowledge.alteryx.com/index/s/article/Auto-Answer-Y-or-N-in-Run-Command-1583460181499

[vj28]

@KGT It's not clear to me as to how this solves my problem.

I need to dynamically pick the excel file who's macro I need to run. The name of the excel file is not known beforehand, and is calculated by alteryx during running the workflow.

Basically similar to how the dynamic input tool takes its input file path from a field, I want the run tool to also take in the name of the file who's macro I need to run dynamically. The name of the macro is fixed, the name of the file is not. 

Thanks for the response!

[KGT]

Maybe I'm missing something as I thought it was exactly what you needed. I'll break it down, this is direct, but not meant to come off as direct as it does...

So, the filename is in the workflow?

And you have a vbs script that runs the macro, however need to put the filename into that vbs script?

Then construct the vbs script in the alteryx workflow and then trigger it with the Run Command by writing it out, and then executing it. The Run Command allows you to have the same file in the "Output/Write" box and the Command box.

So, grab your vbs script and put it in as an input to the workflow (most likely Text Input), then replace the filename in the script with the filename in the workflow (Find/Replace, formula etc), then use the output option of the Run Command to write the script out as today.vbs or something, and call that script in the same Run Command.

[vj28]

@KGT  Oh I see, you're suggesting I copy all the lines of code from my vbs script into a text input tool, use the formula tool to find and replace file name and then paste the resultant code into a newly created .vbs file and run that vbs file with the run tool. Is that it? 

[KGT]

Yep, except no need to paste it back in to a new vbs file, the Run Command will take care of that for you. I happened to see this now at 7pm, and so can build out a little example.

So, the vbs script is in a text input (or can be in a text file, but no need). You get your filename, and replace the dummy filename in the script. Then write out using the run command and run what you have written out.

Note: The script in there is just the first example that came up when I searched something like vbs script to run an excel macro, I have no idea if the script works or is valid, so use your own. It's just for the example.