Calling xla argumented macro from VBScript

L

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
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top