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 wrote:
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