在Excel中运行宏的VBScript引发错误 [英] VBScript that runs a macro in excel throws an error

问题描述

我有一个vbscript，可打开excel工作簿并在特定日期的特定时间在内部运行特定的宏.在这里

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)

，其余脚本不会执行.

有什么想法/帮助吗?

推荐答案

尝试从宏中删除语句以关闭工作簿，并将其添加到VBScript中.另外，不要忘记退出应用程序对象，否则您隐藏的Excel实例将一直在内存中徘徊，因为在脚本终止时不会自动将其丢弃.

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
...

参数 False 将阻止保存对工作簿的更改.如果要自动保存更改，请将其更改为 True .

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

这篇关于在Excel中运行宏的VBScript引发错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！