Dynamic Filename

[RichardAlt]

Hi Guys!

I'm creating a simple workflow with Run Command Tool, I need to run the macro inside the macro-enabled file. But I want my workflow to be dynamic, So I've inserted a File Browser Tool to select the ".xlsm" file and run the macro using Run Command Tool.

And this is my VBScript:

dim fso: set fso = CreateObject("Scripting.FileSystemObject")
dim CurrentDirectory	
Set objExcel = CreateObject("Excel.Application")
CurrentDirectory = fso.GetAbsolutePathName(ThisWorkbook.Path)
Set objWorkbook = objExcel.Workbooks.Open(CurrentDirectory & "\*.xlsm")
objExcel.Application.Visible = True
objExcel.Application.Run "ClearContents"
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

For eg. I've selected the ".xlsm" file using File Browser Tool, How can I use that file and run the macro inside that file that I've select.

As you can see in my VBScript I've set the path and filename static. How can I change that base on the file that I've select?

[OllieClarke · Accepted solution]

Hi @RichardAlt in order to affect the VBS script, you'll need to write it in your workflow. You can create the script with a formula tool - and then you can change the filepath in that script. Then you can output the vbs and run it in the run command tool.

Something like this:

I've attached the workflow too. You might need to fiddle with the formula, runcmd and output to sync it with what you want, but hopefully it'll help 🙂

Ollie

[RichardAlt]

@OllieClarke Thanks! It's working, I've just modified some tools.