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

Thanks.

 

This really helped me out.

Hi,

I am new to Alteryx, I am trying to run a  VBA procedure from alteryx and followed all the instructions in this thread.

the VBScript works fine on its own but when I try to run it through Alteryx events after the Workflow has run it gives me the below error, not sure what is wrong.

 Designer x64 The Designer x64 reported: Error running Event #1: Failed to run external program "O:\model hospital\Modelling BenReal\UoR\UoR Alteryx\Uor Try.vbs": %1 is not a valid Win32 application. ¶ (193)

I tried saving the vbscript as .bat file (on someones suggestion) , the workflow run the event successfully but the vbscript doesn't work.

 

Could anyone please help me with this.

 

VbScript

'This runs the macro below
RunMacro
 
'The RunMacro procedure
Sub RunMacro()
  Dim xl
  Dim xlBook     
  Dim sCurPath
  path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
  Set xl = CreateObject("Excel.application")
  Set xlBook = xl.Workbooks.Open("\\irnhsft\monitor\OpsProd\model hospital\Modelling BenReal\UoR\UoR Master Templates\UoR_Master_Template_Live.xlsm", 0, True)     
  xl.Application.Visible = False
  xl.DisplayAlerts = False   
  xl.Application.run "UoR_Master_Template_Live.xlsm!ExcelToPPT"
  xl.ActiveWindow.close
  xl.Quit
  Set xlBook = Nothing
  Set xl = Nothing
End Sub

Meteor

I've noticed this, too. It is your path in the Event tab for the Command Arguments. Alteryx does not like spaces in the path. Change the following:

"O:\model hospital\Modelling BenReal\UoR\UoR Alteryx\Uor Try.vbs to "O:\model" "hospital\Modelling" "BenReal\UoR\UoR" "Alteryx\Uor" "Try.vbs

Take note of the spaces and what I did.

Looks like I missed the first space after model

Hi CokeLuke,

 

I tried your suggestion and changed the path yet it stills give me the same error.

 

 Designer x64 The Designer x64 reported: Error running Event #1: Failed to run external program "O:\model" "hospital\Modelling" "BenReal\UoR\UoR" "Alteryx\Uor" "Alteryx" "vbs.vbs": %1 is not a valid Win32 application. ¶ (193)

 

 

Asteroid

 

I'm attempting to have Alteryx run a VBScript to update the contents of a file every day. We have a Sharepoint list that both vendors and I access regularly, and I have a query for that list set up in an Excel document. I'd like to pull in the data from that list on a daily basis but can't access the information directly from Sharepoint (IT has confirmed that there is something about the configuration of the Sharepoint list, which is accessible through multiple forms of authentication for outside vendors, that makes it inaccessible to Alteryx.)

 

My workaround is to have a VBScript that opens the file, runs a macro to refresh the query, and then saves the book. This way the data is updated and can be brought into Alteryx later. Unfortunately I don't have a machine that can run 24/7 and therefore schedule the query -- so this post first seemed like a bolt from Heaven!

 

I have created a macro that refreshes the query, and this works fine. If I make a VBScript to execute the macro, this also works fine. And if I run a simple Alteryx workflow with just one text input tool as a dummy to execute the VBScript on completion, this also works fine as long as I have the workflow saved to my machine.

 

Once I attempt to upload the workflow to our company's internal gallery, the workflow times out and never runs the VBScript. I imagine this is because of some issue where the server isn't actually able to run Wscript.exe, or else the server isn't recognizing the command "Wscript.exe". Alternatively it may not be finding the file, which is located on a shared drive I have mapped to Y: within Windows (although I gave Alteryx the UNC file path as the optional parameter for the Wscript command.)

 

One thing I'll need to figure out is Sharepoint authentication, but when I ran a similar workflow with a VBScript on a different workbook that just updated the contents of one cell with no query, it still wouldn't run.

 

Any insights on how I can get more information? I'd like to help give my IT team some more to go on since they're not as conversant with VBScripts.

See below for the screenshot of my events screen in Alteryx (again, works fine on my client machine).

 

Capture.JPG

 

 

 

Meteor

Is 30 seconds still enough time for it to execute on the server?

Meteor

@shehnazmulla

I thought yours was supposed to end with Uor Try.vbs in the file path. Error message doesn't indicate that.

Asteroid
Changing the timeout to longer doesn't change the result - and even making a different script that only updates one cell on the sheet with the timestamp doesn't work either.
Meteor

I wouldn't use the Y: path, instead use the UNC file path since it is not running from your computer any longer.
Test it out by pointing to a file and doing something to it.

 

So you exported the list into MS Excel and refresh your template file I assume.
Are you aware of the power of MS Access with SharePoint? Unlike MS Excel, changes in MS Access is reflected in SharePoint list automatically.

Asteroid
The screenshot may show Y: but in at least initial testing I was using UNC. The screenshot shows Y: probably because I'd been beating my head against the wall and was sloppy when I took the picture for the post!

I'm unfamiliar with the capabilities of Access wrt to SharePoint but am reluctant to add it into the heap. I may have misunderstood Garrett's post but I thought he was referring to running the workflow in the Gallery. Looks like I may have been reading into the post a bit much.
Labels