community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Call VBA macro from Alteryx

Asteroid

@CokeLuke,

 

Found this thread in my inbox. As it turns out, there was a restriction on the Alteryx server that blocked it from running WScript.exe

 

IT had never tried this before and the person who runs security was never looped into the discussion. About 6 weeks later, we happened to bring it up when she was around and she said "Oh, yeah, of course this wouldn't work."

 

-_-

Meteor

Hi,

 

Thank you for posting this, am having a problem with VB script execution, in my current workflow trying to exec vbscript file remotely, 

I tried using in 'Event' and as well as using Run Command tool but getting same error. I also used wscript.exe and cscript.exe but no luck.

 

If I open this Test.vbs file directly from original server it's working fine, it creating a another csv file and that I want but I want to open (execute) this .vbs file withing Alteryx workflow.

 

Many thanks....

 

Capture.PNGCapture2.PNG

 

 

Meteor

@ppatel

Remove the open and close quotes. I only use them when there are actual blank spaces in the path.

Atom

@ScottS28,

Would you know which restriction exactly blocked the server from running WScript.exe?

Alteryx Certified Partner

I've found this thread really helpful in trying to set up my workflow that calls a VBA macro. I've found the best set up has been to use wscript.exe to run a VB script (which has slightly different syntax from VBA) that then opens an Excel file that contains the VBA macro and runs it. Unfortunately, now I really do find myself stuck with this error message coming up. I know this is more a Windows/VB question, but has anyone seen this before and able to help?

 

This is frustrating because the script does manage to open the .xlsm file before getting to this error message. And when I manually go into Excel and run the macro, it runs! (So it's not disabled or unavailable.)

macroerrormsg.PNG

Meteor

Line 21 should be the macro's name, not the path to your file plus the macro's name.

 

Normally, I call mine like this:

'Run macro
xlApp.Run "MacroNameToRun"

Meteor

Might be a setting on your Alteryx Server side. Contact your Alteryx Server admin to investigate.

Alteryx Certified Partner

Thanks for your response, @CokeLuke  That is exactly how my code looks as well:

objExcel.Run "macro3"

 

But the error message gives the full path, probably to confirm that it is looking in the right place.

 

I am running this on Designer on my desktop - no server is involved.

Alteryx Certified Partner

Got this working! Looks like there were 2 things that needed to be fixed:

1. I needed to change the call to objExcel.Run "filename.xlsm!Macroname" as the macro is stored in a different file and therefore this is how Excel sees it.

2. The macro was indeed not visible... because Excel had opened the two files in different instances. To fix that, I needed to do some funky registry stuff (just like in here: https://support.microsoft.com/en-us/help/3165211/how-to-force-excel-to-open-in-a-new-instance-by-def... but entering 0 rather than 1 for the very last instruction).

Atom

@Garrett

 

Would you expand on how you build the code for the .vbs file? I am having issues putting it all together. Especially with the .exe file as well.

 

Thank you!

Labels