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

Alteryx Partner

Hi all,

 

I have a problem, which I hope you guys can help me :)

 

We use a VBA macro to update a number of SAP BO queries and saves them as text files (for an SQL DB). Alteryx does not seem to have access rights to update a SAP BO query directly, but we can update the queries using excel VBA which uses our windows credentials.

 

My questions is: is it possible to call an VBA macro through Alteryx ?

 

We have an Alteryx server, so we could fully-automate our data load, currently someone has to press a button every day and is not able to use Excel when the files are updating.   

 

I look forward to your answer.

 

Br,

Simon

Highlighted
Alteryx Certified Partner

I use the "Events" function found in Workflow-Configuration to execute VBA script(s) after an Alteryx workflow has run successfully. I've also seen it done with a Run Command tool.

Alteryx Partner

Hi Garret

 

Thank you for reply.

 

How do you set up the Run Command/events ?

 

Could I possible see a screenshot of a Run command/events on a vba macro ?

 

Br,

Simon

Alteryx Certified Partner

Here is a shot of the Event (which executes ScriptFromAlteryx.vbs using wscript.exe):

 

 

Alteryx Certified Partner

Here is a shot of the Event (which executes ScriptFromAlteryx.vbs using wscript.exe):

Event.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

And here is a shot of the VBS code (which opens "FormattedExcel.xlsm" in Excel, executes the macro called "Format", saves the workbook, and closes Excel.

 

 

 

Alteryx Certified Partner

Here is a shot of the Event (which executes ScriptFromAlteryx.vbs using wscript.exe):

Event.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

And here is a shot of ScriptFromAlteryx.vbs (which opens "FormattedExcel.xlsm" in Excel, executes the macro called "Format", saves the workbook, and closes Excel.

VBS_to_Execute_Excel_Macro.PNG

 

 

Alteryx Partner

Hi Garret 

 

Thank you for your reply. 

 

What does the wscript.exe file do ? 

 

Br, 

Simon

Alteryx Certified Partner

wscript.exe is a 'Windows script host'. Basically just used to run the vbs script.

HI Garrett,

 

Good Day!!

 

First Would like to say big thanks to you for sharing the Events and VBA script Screen shot . Could you please advise how to open the VBS script in alteryx . 

 

Thanks 

Alteryx Certified Partner

Can you provide a bit more context about what you're trying to do Ganesh0010?

 

If you really just want to open a .VBS file into Alteryx as data, you can use an Input Data tool and configure it to open it like you might a .TXT file, by telling Alteryx that it is a delimited text file with no delimiter:

Capture.PNG

 

However, I suspect you might be trying to do something else; let me know if this is the case.

 

If it helps, I usually operate in the reverse. I use Text Input, Formula tool, etc. to create the data for the .VBS file, then I use the Output Data tool to write the .VBS file to disk, before calling it from the "Events" tab.

Labels