Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Run VB script

murrayjl03
7 - Meteor

Hi!

I have a workflow which checks to see if a status is marked as complete and if so, adds the "date completed" as today's date. After outputting the file I want to run a VB script on this file which calls the macro within the output file to do some basic formatting.

 

within the "Run command! option i have cscript FormatScript.vbs within the "Run External Program Command" and the output file from above within the "Read Results" and i get the error "When there is an incoming connection this tool requires an output file to be configured"

I have attached the workflow. 

As i cant upload the VB script or macro within the output i have saved/attached the output file as a normal workbook and contents of the script and macro can be found below:

 

VB Script "FormatScript":

 

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\jmurray8\Desktop\Consolidated Jobs.xlsm")

objExcel.Application.Visible = True

objExcel.Application.Run "FormatScript"
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

 

Macro within excel:

 Sub FormatScript()
Range("A1:C1").Interior.ColorIndex = 5
Range("A1:C1").Font.Color = vbWhite
End Sub

 

Thanks,

Jess

 

9 REPLIES 9
jdunkerley79
ACE Emeritus
ACE Emeritus

Try the attached.

 

This writes out the header a temporary CSV file. The Run command expects the data to be exported to be used by the command.

In addition, I split the arguments from the command line for the cscript command but am not sure if that would matter,

 

If this doesn't work, the next option be to call Excel.exe with a few command line arguments to do the same as the VBS.

murrayjl03
7 - Meteor

Hi,

I tried your attached and getting the error "The external program "cscript" returned error code: 1".

If it changed the "Command arguments" to " cscript C:\Users\jmurray8\Desktop\FormatScript.vbs", it would run however the formatting changes don't seem to be applied to the Consolidated Jobs file. 

Apologies for asking however I am not sure how i would create the Excel.exe, could you point me in the right direction?

Thanks,

Jess

jdunkerley79
ACE Emeritus
ACE Emeritus

Had a quick play at lunch. Got it to work with a VBS file - am sure possible with Excel.exe directly but can't remember the sequence.

 

The attached packaged workbook has a demo Excel file but you will need to add a script.vbs to the same folder with the following content (basically the same as yours):

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

 

murrayjl03
7 - Meteor

Thank you so much!

ppatel
7 - 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

 

 

 

Bhavika
8 - Asteroid

@jdunkerley79 

Hi there,

 

I went through the post. I tried replicating your workflow. However, the vbs file runs well without any error when i execute it out of Alteryx. However, when i run the Alteryx workflow i always get the below error:

Capture1.PNG

 

Please help. I have been struggling to execute vbs script through Alteryx since over 3 days now. But, had no luck.

 

Thanks.

 

jdunkerley79
ACE Emeritus
ACE Emeritus

@Bhavika 

 

In the command tool you need to have:

cscript.exe

as the command and then the path to you VB Script as the argument (as you already have).

 

Hope that makes it work for you.

psubramanian
7 - Meteor

Hi,

Can we run a cscript.exe dependent VBA script on Alteryx Gallery?

Karlygash_M
8 - Asteroid

Hello @jdunkerley79 

Is cscript.exe internal run command in Alteryx? I m trying to run this workflow, have an issue 

Karlygash_M_0-1681287220666.png

 

Labels