Alteryx Designer

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

Run VB script

Highlighted
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

 

Highlighted
16 - Nebula
16 - Nebula

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.

Highlighted
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

Highlighted
16 - Nebula
16 - Nebula

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

 

Highlighted
7 - Meteor

Thank you so much!

Highlighted
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

 

 

 

Highlighted
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.

 

Highlighted
16 - Nebula
16 - Nebula

@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.

Labels