如何使用Windows任务调度器设置xlsm文件的循环调度

I have a xlsx macro 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 . . .

应用程序。定时。

But i realize that keeping the xlsm file open is inconvenient

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

5 个解决方案

#1

Better to use a vbs as you indicated

最好像您说的那样使用vbs

1. Create a simple vbs, which is a text file with a .vbs extension (see sample code below)
2. 创建一个简单的vbs，它是一个扩展名为.vbs的文本文件(参见下面的示例代码)
3. Use the Task Scheduler to run the vbs
4. 使用任务调度程序运行vbs
5. Use the vbs to open the workbook at the scheduled time and then either:
   • use the Private Sub Workbook_Open() event in the ThisWorkbook module to run code when the file is opened
   • 在这个工作簿模块中使用私有子Workbook_Open()事件来运行文件打开时的代码。
   • more robustly (as macros may be disabled on open), use Application.Run in the vbs to run the macro
   • 更健壮的是(因为宏可能在打开时被禁用)，使用应用程序。在vbs中运行以运行宏
6. 使用vbs在预定的时间打开工作簿，然后:使用ThisWorkbook模块中的Private Sub Workbook_Open()事件来运行打开更健壮的文件时的代码(因为宏可能在打开时被禁用)，使用Application。在vbs中运行以运行宏

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

在Windows任务调度器上运行Excel，请参见后面的方法示例。

sample 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

#2

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.

宏的自动执行可以在VB脚本文件的帮助下完成，该脚本文件在指定时间由Windows任务调度器调用。

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.

记住要用你想要打开的工作簿的名字替换“你的工作簿”，用你想要运行的宏的名字替换“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 Task Scheduler(控制面板->管理工具——>任务调度程序)执行“cscript”。exe RunExcel。根据“自动为你。

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

请注意，宏的路径应该是正确的格式，并且在单引号内，如:

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

#3

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 xlsm not 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 excel(C:\ \ 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.

操作，使用Cscript将vbscript设置为open。exe，这样它将在后台执行，而不会被vbcript启用的错误处理挂起。

#4

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. 确保.vbs文件是正确的
3. set the Action tab correctly in Task Scheduler
4. 在任务调度程序中正确设置动作选项卡
5. don't turn on "Run whether user is logged on or not"
6. 不要打开“无论用户是否登录运行”

IN MORE DETAIL...

在更多的细节……

1. Here is an example .vbs file:
2. 这里有一个例子。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 
'

`

”

2. In the Action tab (Task Scheduler):
3. 在Action选项卡(任务调度程序)中:

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

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

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

设置添加参数(可选):= C:\ myvsfile .vbs

3. Finally, don't turn on "Run whether user is logged on or not".
4. 最后，不要打开“不管用户是否登录”。

That should work.

这应该工作。

Let me know!

让我知道!

Rod Bowen

杆博文

#5

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()“要运行的宏(不需要在这个模块中，只在这个工作簿末子中)。

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办公室\ \ Office16根)。然后设置“添加参数(可选):作为启用宏的工作簿的文件路径。记住，Excel的路径和工作簿的路径都应该用双引号。

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

*参见社区编辑的Rich示例，获取windows调度器屏幕的图像。