vba Excel OnTime 事件计划程序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18358214/
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
Excel OnTime Events Scheduler
提问by YoYue
I need to run my excel VBA daily at a specific time. I google the ontime method, however I dont get a comprehensive understanding from it. I would bring up this to clean up my confusion of ontime method.
我需要每天在特定时间运行我的 excel VBA。我在谷歌上搜索了 ontime 方法，但是我没有从中得到全面的理解。我会提出这个来消除我对准时方法的困惑。
Does the containing excel workbook have to be open for ontime method before it runs. If yes, Is there a way to open the excel workbook automatically at a specific time.I know it mite be done by Timer or a Windows task Scheduler. Could someone work me through this. Is my current code properly constructed for automated task scheduler?
包含的 excel 工作簿是否必须在运行前为 ontime 方法打开。如果是，有没有办法在特定时间自动打开 Excel 工作簿。我知道它可以由计时器或 Windows 任务计划程序完成。有人能帮我解决这个问题吗？我当前的代码是否为自动任务调度程序正确构建？
My current code looks like this:
我当前的代码如下所示：
Sub StartTimer()
Application.OnTime EarliestTime:=TimeValue("11:15:00"), Procedure:="rune", _
Schedule:=True
End Sub
Sub rune()
SourceOneUpdate
SourceTwoUpdate
SourceThreeUpdate
GenerateReport
End Sub
Private Sub workbook_open()
StartTimer
End Sub
This is based on the idea from this post: http://www.cpearson.com/excel/OnTime.aspxwhich mite be helpful. Even this workbook is open, its not running automatically. Could someone help me on this to see why this is not working properly.
这是基于这篇文章中的想法：http: //www.cpearson.com/excel/OnTime.aspx哪些螨有帮助。即使这个工作簿是打开的，它也不会自动运行。有人可以帮我看看为什么这不能正常工作。
Thanks in advance.
提前致谢。
回答by kpark
just to expand on d-stroyer's comment.
只是为了扩展 d-stroyer 的评论。
You first need to make sure your macro setting is enabled at all times because this will ensure that everytime your workbook opens, the macro runs without any notification or confirmation.
您首先需要确保始终启用您的宏设置，因为这将确保每次打开工作簿时，宏都会在没有任何通知或确认的情况下运行。
To do this,
Excel Options > Trust Center > Trust Center Settings (Button) > Macro Settings > Enable All macros > OK
为此，
Excel 选项 > 信任中心 > 信任中心设置（按钮）> 宏设置 > 启用所有宏 > 确定
Now that your macro is enabled, you need to ensure the macro will run as soon as the workbook opens. So, go to your VB editor and open the ThisWorkbook module > Create a workbook_open() event and copy and paste your "OnTime code" into workbook_open event > Save & Close
现在您的宏已启用，您需要确保宏将在工作簿打开后立即运行。因此，转到您的 VB 编辑器并打开 ThisWorkbook 模块 > 创建一个 workbook_open() 事件并将您的“OnTime 代码”复制并粘贴到 workbook_open 事件中 > 保存并关闭
Now, everytime you open the workbook, the workbook should run the desired code at 8:47.
现在，每次打开工作簿时，工作簿都应在 8:47 运行所需的代码。
Go to Task Scheduler, on the right click on "Create Basic Task...". Give the task a name and click next. Select a trigger (Daily in the case you mentioned here) and click next. Set the time and recurrence period and click next. In the Action, select "Start a program" and click next. In the Program/script text box browse for your Excel file and click next (leave the other text boxes empty). Click on Finish. I just tried this on my PC now, and it works.
转到任务计划程序，右键单击“创建基本任务...”。为任务命名并单击下一步。选择一个触发器（在您在这里提到的情况下为每日），然后单击下一步。设置时间和重复周期，然后单击下一步。在操作中，选择“启动程序”，然后单击下一步。在程序/脚本文本框中，浏览您的 Excel 文件并单击下一步（将其他文本框留空）。单击完成。我现在刚刚在我的电脑上试过这个，它有效。
PS: Make sure you set the scheduler to open the excel file BEFORE 8:47 (so maybe 8:46).
PS：确保您将调度程序设置为在 8:47（所以可能是 8:46）之前打开 Excel 文件。
Cheers,
kpark
干杯，
kpark
EDIT: try running this to see if your OnTime is working..
编辑：尝试运行它以查看您的 OnTime 是否正常工作..
Sub RunOnTime()
Application.OnTime Now + TimeSerial(0, 0, 10), "theSub"
End Sub
