VBScript that runs a macro in excel throws an error

Question

I have a vbscript that opens an excel workbook and runs a particular macro inside at a particular time on a particular day. Here it is

Option Explicit

Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False

While not (Month(Now()) = 11 and Day(Now()) = 17 and Hour(Now()) = 9 and Minute(Now()) = 1)
Wend

Set xlBook = xlApp.Workbooks.Open("C:\Temp\Property by Peril.xlsm")
xlApp.Application.Run "'Property by Peril.xlsm'!Main"

Set xlBook = Nothing
Set xlApp = Nothing

WScript.Echo "Finished."
WScript.Quit

It runs fine up to and including the opening of the workbook and the running of the macro. However, immediately after the macro runs, I get an error

Microsoft VBScript runtim error: Unknown runtime error   (on line 16)

and the rest of the script does not get executed.

Any thoughts/help?

Have you tried running the macro from Excel directly? It may not be your VBScript failing but the macro itself..? — user2140173
– user2140173, Commented Nov 17, 2014 at 15:45
Yes, @vba4all , good thinking. But I did run it directly and it works just fine on its own. — Matt Cremeens
– Matt Cremeens, Commented Nov 17, 2014 at 15:45
have you tried removing the workbook name? it's not necessary just leave the macro name (.Run "Main")? Also which one is line 16? — user2140173
– user2140173, Commented Nov 17, 2014 at 15:53
I just tried your suggestion, @vba4all , but with the same result. It opens the workbook and runs the macro, but then stops there. Line 16 is the .Run line, which is odd because that line runs fine. It's just the ones after that don't run and the error is triggered. — Matt Cremeens
– Matt Cremeens, Commented Nov 17, 2014 at 15:59
hm.. maybe you're missing the xlApp.close line or something that will close the file before setting references to Nothing? can you share the code from Main? — user2140173
– user2140173, Commented Nov 17, 2014 at 16:07

Ansgar Wiechers · Accepted Answer · 2014-11-17 19:10:07Z

2

Try removing the statement to close the workbook from the macro and adding it to the VBScript. Also, don't forget to quit the application object, otherwise your hidden Excel instance will keep lingering in memory, because it's not automatically discarded when the script terminates.

...
xlApp.Application.Run "'Property by Peril.xlsm'!Main"

xlBook.Close False
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing
...

The parameter False will prevent changes to the workbook from being saved. Change it to True if you want changes to be automatically saved.

answered Nov 17, 2014 at 19:10

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bilbo_strikes_back · Accepted Answer · 2014-11-17 16:59:19Z

1

The following code ran successfully on my system.

Option Explicit

Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False

Set xlBook = xlApp.Workbooks.Open("C:\Temp\Property by Peril.xlsm")
xlApp.Application.Run "Main"

xlBook.Close False
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

WScript.Echo "Finished."
WScript.Quit

answered Nov 17, 2014 at 16:59

bilbo_strikes_back

5913 silver badges10 bronze badges

Collectives™ on Stack Overflow

VBScript that runs a macro in excel throws an error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related