vba 如何使用 Windows 任务计划程序为 xlsm 文件设置重复计划

How to set recurring schedule for xlsm file using Windows Task Scheduler

提问by Andrei Vasilev

I have a xlsxmacro enabled file . How can I set it in the task manager so that everyday at 9 AM task manager would open the workbook, fire the macro and close the workbook.

我有一个xlsx启用宏的文件。如何在任务管理器中设置它，以便每天上午 9 点任务管理器打开工作簿，触发宏并关闭工作簿。

So far i am using

到目前为止我正在使用

Application.OnTime . . .

Application.OnTime . . .

But i realize that keeping the xlsm file open is inconvenient

但我意识到保持 xlsm 文件打开很不方便

回答by brettdj

Better to use a vbsas you indicated

最好按照您的指示使用vbs

1. Create a simple vbs, which is a text file with a .vbsextension (see sample code below)
2. Use the Task Scheduler to run the vbs
3. Use the vbsto open the workbookat the scheduled time and then either:
   • use the Private Sub Workbook_Open()event in the ThisWorkbookmodule to run code when the file is opened
   • more robustly (as macros may be disabled on open), use Application.Runin the vbsto run the macro

1. 创建一个 simple vbs，它是一个扩展名为.vbs的文本文件（参见下面的示例代码）
2. 使用任务计划程序运行 vbs
3. 使用在预定时间vbs打开workbook，然后：
   • 使用模块中的Private Sub Workbook_Open()事件在ThisWorkbook文件打开时运行代码
   • 更健壮（因为宏可能在打开时被禁用），Application.Run在vbs运行宏时使用

See this example of the later approach at Running Excel on Windows Task Scheduler

请参阅在 Windows 任务计划程序上运行 Excel 中后一种方法的示例

sample vbs

示例 vbs

Dim ObjExcel, ObjWB
Set ObjExcel = CreateObject("excel.application")
'vbs opens a file specified by the path below
Set ObjWB = ObjExcel.Workbooks.Open("C:\temp\rod.xlsm")
'either use the Workbook Open event (if macros are enabled), or Application.Run

ObjWB.Close False
ObjExcel.Quit
Set ObjExcel = Nothing

回答by rchacko

I referred a blog by Kim for doing this and its working fine for me. See the blog

我推荐了 Kim 的博客来做这件事，它对我来说很好用。看博客

The automated execution of macro can be accomplished with the help of a VB Script file which is being invoked by Windows Task Scheduler at specified times.

宏的自动执行可以在 Windows 任务计划程序在指定时间调用的 VB 脚本文件的帮助下完成。

Remember to replace 'YourWorkbook' with the name of the workbook you want to open and replace 'YourMacro' with the name of the macro you want to run.

请记住将“YourWorkbook”替换为要打开的工作簿的名称，并将“YourMacro”替换为要运行的宏的名称。

See the VB Script File (just named it RunExcel.VBS):

见 VB 脚本文件（只是将其命名为 RunExcel.VBS）：

    ' Create a WshShell to get the current directory
Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

' Create an Excel instance
Dim myExcelWorker
Set myExcelWorker = CreateObject("Excel.Application") 

' Disable Excel UI elements
myExcelWorker.DisplayAlerts = False
myExcelWorker.AskToUpdateLinks = False
myExcelWorker.AlertBeforeOverwriting = False
myExcelWorker.FeatureInstall = msoFeatureInstallNone

' Tell Excel what the current working directory is 
' (otherwise it can't find the files)
Dim strSaveDefaultPath
Dim strPath
strSaveDefaultPath = myExcelWorker.DefaultFilePath
strPath = WshShell.CurrentDirectory
myExcelWorker.DefaultFilePath = strPath

' Open the Workbook specified on the command-line 
Dim oWorkBook
Dim strWorkerWB
strWorkerWB = strPath & "\YourWorkbook.xls"

Set oWorkBook = myExcelWorker.Workbooks.Open(strWorkerWB)

' Build the macro name with the full path to the workbook
Dim strMacroName
strMacroName = "'" & strPath & "\YourWorkbook" & "!Sheet1.YourMacro"
on error resume next 
   ' Run the calculation macro
   myExcelWorker.Run strMacroName
   if err.number <> 0 Then
      ' Error occurred - just close it down.
   End If
   err.clear
on error goto 0 

oWorkBook.Save 

myExcelWorker.DefaultFilePath = strSaveDefaultPath

' Clean up and shut down
Set oWorkBook = Nothing

' Don't Quit() Excel if there are other Excel instances 
' running, Quit() will shut those down also
if myExcelWorker.Workbooks.Count = 0 Then
   myExcelWorker.Quit
End If

Set myExcelWorker = Nothing
Set WshShell = Nothing

You can test this VB Script from command prompt:

您可以从命令提示符测试此 VB 脚本：

>> cscript.exe RunExcel.VBS

Once you have the VB Script file and workbook tested so that it does what you want, you can then use Microsoft Task Scheduler (Control Panel-> Administrative Tools--> Task Scheduler) to execute ‘cscript.exe RunExcel.vbs' automatically for you.

一旦您对 VB 脚本文件和工作簿进行了测试以使其执行您想要的操作，您就可以使用 Microsoft 任务计划程序（控制面板-> 管理工具--> 任务计划程序）自动执行“cscript.exe RunExcel.vbs”你。

Please note the path of the macro should be in correct format and inside single quotes like:

请注意，宏的路径应该采用正确的格式并在单引号内，例如：

strMacroName = "'" & strPath & "\YourWorkBook.xlsm'" & 
"!ModuleName.MacroName"

回答by Rod Bowen

Three important steps - How to Task Schedule an excel.xls(m) file

三个重要步骤 - 如何安排 excel.xls(m) 文件的任务

simply:

简单地说：

1. make sure the .vbs file is correct
2. set the Action tab correctly in Task Scheduler
3. don't turn on "Run whether user is logged on or not"

1. 确保 .vbs 文件正确
2. 在任务计划程序中正确设置操作选项卡
3. 不要打开“无论用户是否登录都运行”

IN MORE DETAIL...

更详细...

1. Here is an example .vbs file:

1. 这是一个示例 .vbs 文件：

`

`

'   a .vbs file is just a text file containing visual basic code that has the extension renamed from .txt  to .vbs

'Write Excel.xls  Sheet's full path here
strPath = "C:\RodsData.xlsm" 

'Write the macro name - could try including module name
strMacro = "Update" '    "Sheet1.Macro2" 

'Create an Excel instance and set visibility of the instance
Set objApp = CreateObject("Excel.Application") 
objApp.Visible = True   '   or False 

'Open workbook; Run Macro; Save Workbook with changes; Close; Quit Excel
Set wbToRun = objApp.Workbooks.Open(strPath) 
objApp.Run strMacro     '   wbToRun.Name & "!" & strMacro 
wbToRun.Save 
wbToRun.Close 
objApp.Quit 

'Leaves an onscreen message!
MsgBox strPath & " " & strMacro & " macro and .vbs successfully completed!",         vbInformation 
'

`

`

1. In the Action tab (Task Scheduler):

2. 在操作选项卡（任务计划程序）中：

set Program/script: = C:\Windows\System32\cscript.exe

设置程序/脚本：= C:\Windows\System32\cscript.exe

set Add arguments (optional): = C:\MyVbsFile.vbs

设置添加参数（可选）：= C:\MyVbsFile.vbs

1. Finally, don't turn on "Run whether user is logged on or not".

3. 最后，不要打开“无论用户是否登录都运行”。

That should work.

那应该工作。

Let me know!

让我知道！

Rod Bowen

罗德鲍文

回答by Rich

Code below copied from -> Here

下面的代码复制自 ->此处

First off, you must save your work book as a macro enabled work book. So it would need to be xlsmnot an xlsx. Otherwise, excel will disable the macro's due to not being macro enabled.

首先，您必须将工作簿保存为启用宏的工作簿。所以它需要xlsm不是xlsx. 否则，由于未启用宏，excel 将禁用宏。

Set your vbscript (C:\excel\tester.vbs). The example sub "test()" must be located in your modules on the excel document.

设置你的 vbscript (C:\excel\tester.vbs)。示例子“test()”必须位于 excel 文档的模块中。

dim eApp
set eApp = GetObject("C:\excel\tester.xlsm")
eApp.Application.Run "tester.xlsm!test"
set eApp = nothing

Then set your Schedule, give it a name, and a username/password for offline access.

然后设置您的日程安排，为其命名，以及用于离线访问的用户名/密码。

Then you have to set your actions and triggers.

然后你必须设置你的动作和触发器。

Set your schedule(trigger)

设置您的日程安排（触发）

Action, set your vbscript to open with Cscript.exe so that it will be executed in the background and not get hung up by any error handling that vbcript has enabled.

操作，将您的 vbscript 设置为使用 Cscript.exe 打开，以便它将在后台执行并且不会被 vbcript 启用的任何错误处理挂起。

回答by Justin

I found a much easier way and I hope it works for you. (using Windows 10 and Excel 2016)

我找到了一个更简单的方法，我希望它对你有用。（使用 Windows 10 和 Excel 2016）

Create a new module and enter the following code: Sub auto_open() 'Macro to be run (doesn't have to be in this module, just in this workbook End Sub

创建一个新模块并输入以下代码： Sub auto_open() '要运行的宏（不必在此模块中，只需在此工作簿中 End Sub

Set up a task through the Task Scheduler and set the "program to be run as" Excel (found mine at C:\Program Files (x86)\Microsoft Office\root\Office16). Then set the "Add arguments (optional): as the file path to the macro-enabled workbook. Remember that both the path to Excel and the path to the workbook should be in double quotes.

通过任务计划程序设置任务并设置“要作为运行的程序”Excel（在 C:\Program Files (x86)\Microsoft Office\root\Office16 找到我的）。然后将“添加参数（可选）：作为启用宏的工作簿的文件路径。请记住，Excel 的路径和工作簿的路径都应该用双引号引起来。

*See example from Rich, edited by Community, for an image of the windows scheduler screen.

*有关 Windows 调度程序屏幕的图像，请参阅由社区编辑的 Rich 示例。