Not able to run VB Script from Alteryx, but running in Excel

[Parthasarathi]

Hi All,

I am trying the below vb script to copy required columns data from a sheet to another existing sheet in the same workbook. I am executing this .vbs file using Run Command component, but 

Sub foo1()
Dim src As Worksheet
Dim trg As Worksheet
Dim LastRow As Long

Set src=ThisWorkbook.Worksheets("Summary")
Set trg = ThisWorkbook.Worksheets("Sheet1")

src.Range("D:D").Copy Destination:=trg.Range("A1")
src.Range("E:E").Copy Destination:=trg.Range("B1")
src.Range("F:F").Copy Destination:=trg.Range("C1")
src.Range("G:G").Copy Destination:=trg.Range("D1")
End Sub

Getting error like below:

But the same script working in excel under developer menu. But not from Alteryx. Please help, thanks in advance

[dYoast]

@Parthasarathi 

When you run the script in Excel, it knows the object to perform its tasks on.

When you try to run it independently, it has no idea of the object it is supposed to work with.

You would need to add to the script to declare the object Excel and to open the object.

[Parthasarathi]

@dYoast,

Could you please share me the script to run it from the Alteryx

[Luke_C · Accepted solution]

Hi @Parthasarathi 

Here's an example where I used a vbs file triggered by Alteryx to run a macro within a workbook. You can adapt to your needs. Hope this helps.

In the below example, the 'file path' is populated dynamically by the workflow. 

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\File Path")
objExcel.Application.DisplayAlerts = False
objExcel.Application.Visible = False
objWorkbook.Application.Run "MacroName"
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close

[Parthasarathi]

@Luke_C,

Could you please share a sample workflow calling this VBS, still I am facing issue to pass the filename as argument to the Run Command component.

[ChrisTX]

Here are some examples for Run Command:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Run-Command-Error-Code-1/m-p/299634#M5...

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/The-Run-Command-Write-Source-amp-Read-Result...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-update-run-command-tool-command...

Chris