Dynamically select which excel file's macro to run

Question

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 · Answer

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.

Write and Run Excel Macro.yxmd

vj28 · Answer

@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 · Answer

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.