Alteryx Designer Desktop Discussions

Parthasarathi · ‎09-12-2022

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 · ‎09-12-2022

@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 · ‎09-12-2022

@dYoast,

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

Luke_C · ‎09-12-2022

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