CODEWITHSUNDEEP
excelvbscriptvba
Monica
Monica

Reputation: 169

Calling macro from VB script

Hi I am trying to call a macro in test.xls using the following VB script

Option Explicit

Dim returnVal 
returnVal = 0
WScript.Echo returnVal

Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("D:\test.xls", 0, True)
xlApp.Run "macro1"
xlBook.Close false
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

WScript.Echo returnVal
''WScript.Quit returnVal

The macro defined in test.xls is : 

Dim returnVal as Boolean
sub macro1()
   returnVal = 1
   Exit Sub
...
End Sub

When I try to run the VB script I get a pop up window with value 0 (that is for the echo I am doing at the start). Then I get a pop up window with value 0 again. Looks like the value from the macro is not being returned.

Where am I going wrong here.

Thanks, Monica

Upvotes: 2

Views: 2555

Answers (1)

SysDragon
SysDragon

Reputation: 9888

Define the macro as a function:

Function macro1()
    macro1 = 1
End Function

And then get the value:

returnVal = xlApp.Run("macro1", "My Application")

Find more examples here:
http://support.microsoft.com/kb/306682

Upvotes: 2

Related Questions