使用 Excel vba 宏通过 Windows 计划任务运行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24129613/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Excel vba Macro to be run through Windows Schedule Task
提问by Jez
I have a Excel spreadsheet which I've set up a timer to run code into a database. If the spreadsheet is open and the time now is the time set within the timeslot then it exports the data into the database
我有一个 Excel 电子表格，我已经设置了一个计时器来将代码运行到数据库中。如果电子表格已打开并且现在时间是时间段内设置的时间，则它将数据导出到数据库中
I use this line in both my subroutine and the workbook_open Application.OnTime TimeValue("22:00:00"), "ExportOpenJobs"
我在我的子程序和 workbook_open 中都使用了这一行 Application.OnTime TimeValue("22:00:00"), "ExportOpenJobs"
This is great for when the spreadsheet is open, but I want to be able to set it through the Windows Schedule task.
这对于电子表格打开时非常有用，但我希望能够通过 Windows 计划任务进行设置。
I'm using Windows Server 2012 as my host pc and where the file is stored. Within the Task Scheduler I set the Action to Start a program and the Program script to the location and the actual *.xlsm file along with the start time for the Task. I set this task 30seconds before the time within the Excel VBA.
我使用 Windows Server 2012 作为我的主机以及存储文件的位置。在任务计划程序中，我将启动程序的操作和程序脚本设置为位置和实际 *.xlsm 文件以及任务的开始时间。我在 Excel VBA 中的时间之前 30 秒设置了此任务。
My problem is that the Windows Task Scheduler runs at the time set, after looking at the Task History I can see the Task Started/Completed and Action Started/Completed often taking around 50mins to complete, but when I check the database the Excel VBA hasn't run.
我的问题是 Windows 任务计划程序在设定的时间运行，在查看任务历史记录后，我可以看到任务启动/完成和操作启动/完成通常需要大约 50 分钟才能完成，但是当我检查数据库时，Excel VBA 没有不跑。
How can I get my Task Scheduler to run the Excel VBA code?
如何让我的任务计划程序运行 Excel VBA 代码？
Within the Windows Server do you actually need to have Excel installed, therefore should it be done on another machine?
在 Windows Server 中，您实际上是否需要安装 Excel，因此是否应该在另一台机器上完成？
回答by Sean W.
Hello what I would do is create this .VBS file and then use Windows Task Scheduler to execute this vbs file at the desired interval.
你好，我要做的是创建这个 .VBS 文件，然后使用 Windows 任务计划程序以所需的时间间隔执行这个 vbs 文件。
Set fso = CreateObject("Scripting.FileSystemObject")
curDir = fso.GetAbsolutePathName(".")
Set myxlApplication = CreateObject("Excel.Application")
myxlApplication.Visible = False
Set myWorkBook = myxlApplication.Workbooks.Open( curDir & "\myTest.xlsm" ) 'Change to the actual workbook that has the Macro
myWorkBook.Application.Run "Module1.HelloWorld" 'Change to the Module and Macro that contains your macro
myxlApplication.Quit
Application.Run Method (Excel)
This example does require Excel to be installed on to the host running the scheduled task. If that host is a Server or a Desktop computer is your choice.
此示例确实需要将 Excel 安装到运行计划任务的主机上。如果该主机是服务器或台式计算机是您的选择。
The Windows Task Scheduler should be done as such for the Action portion of the task:
Windows 任务计划程序应该为任务的操作部分完成：
Action: Start a program
Action: Start a program
Program/script: C:\Windows\SysWOW64\cscript.exe
Program/script: C:\Windows\SysWOW64\cscript.exe
Add arguments (optional): C:\Path_to_your_vbs\Your.vbs
Add arguments (optional): C:\Path_to_your_vbs\Your.vbs
Start in (optional): C:\Path_to_your_vbs\
Start in (optional): C:\Path_to_your_vbs\
回答by DanG
In Windows Task Scheduler set: Action: Start a Program Program Script: The Excel.exe with path in full quotes. Add Arguments: The workbook you want to open with full path in quotes
在 Windows 任务计划程序中设置：操作：启动程序 程序脚本：Excel.exe，路径用全引号括起来。添加参数：要使用引号中的完整路径打开的工作簿
Check out this link: https://answers.microsoft.com/en-us/windows/forum/windows_8-performance/excel-files-can-not-be-opened-in-task-scheduler-in/aa6cf065-09ac-44d8-b6fb-a2f2ecd9b8cc
查看此链接：https: //answers.microsoft.com/en-us/windows/forum/windows_8-performance/excel-files-can-not-be-opened-in-task-scheduler-in/aa6cf065-09ac -44d8-b6fb-a2f2ecd9b8cc
Worked for me after banging my head for a couple hours.
在敲打我的头几个小时后对我来说有效。
