Calling xla argumented macro from VBScript

[Lieven Roelens]

Hi,

I'm trying to call a macro with 1 argument from VBScript
but the VBA code doesn't get executed.
I've no problem when calling a macro without arguments.
Is it possible to call a Excel macro with arguments
through automation?

VBScript:

Const vbNormal = 1 ' window style
DIM objXL, objWb ' Excel object variables

Set objXL = WScript.CreateObject ("Excel.Application")
objXL.WindowState = vbNormal
objXL.Visible = true
Set objWb = objXL.WorkBooks.Open("C:\TEMP\test.xla")
objXL.Run "data_pump","C:\TEMP\test.xml"

xla VBA code

Public Sub data_pump(sFileUri As Variant)
Application.Workbooks.Open (CStr(sFileUri))
End Sub

Thanks for the support,

Lieven

 

[Dave Peterson]

I used this in my VBA code:

Option Explicit
Sub data_Pump(mystr As String)
MsgBox mystr
End Sub


And my workbook was not in c:\temp\test.xla, but this seemed to work ok:

Const vbNormal = 1 ' window style
Dim objXL, objWb ' Excel object variables

Set objXL = CreateObject("Excel.Application")
objXL.WindowState = vbNormal
objXL.Visible = True
Set objWb = objXL.WorkBooks.Open("book1.xls")
objXL.Run "book1.xls!data_Pump", "C:\TEMP\test.xml"

===
I also tested from MSWord, so I dropped the WScript. bit, too.

 

[Lieven Roelens]

Apparently it works with an .xls file and not with
an .xla file.