Excel OnTime事件计划程序 [英] Excel OnTime Events Scheduler

问题描述

我需要在特定时间每天运行我的excel VBA。我google了ontime方法，但是我没有从中得到全面的了解。我会提出来，以清理我的时间方法的混乱。

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.

在运行之前，contains excel工作簿是否必须打开ontime方法。如果是，有没有办法在特定时间自动打开excel工作簿。我知道它是由Timer或Windows任务计划程序完成的。
有人可以通过这个工作。我的当前代码是否适合自动化任务调度程序？

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?

我目前的代码如下所示：

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

这是基于这个帖子的想法： http://www.cpearson.com/excel/OnTime.aspx哪个螨有帮助即使这个工作簿是打开的，它不会自动运行。有人可以帮助我了解为什么这不能正常工作。

This is based on the idea from this post: http://www.cpearson.com/excel/OnTime.aspx which 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.

提前感谢。

推荐答案

只是为了扩展d-stroyer的评论。

just to expand on d-stroyer's comment.

您首先需要确保您的宏设置始终启用，因为这将确保每次您的工作簿打开时，宏都会运行，而不会发生任何通知或确认。

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.

为此，可以使用
Excel选项>信任中心>信任中心设置（按钮）>宏设置>启用所有宏> OK

To do this,
Excel Options > Trust Center > Trust Center Settings (Button) > Macro Settings > Enable All macros > OK

现在，您的宏已启用，您需要确保宏一旦工作簿打开就会运行。所以，去你的VB编辑器并打开ThisWorkbook模块>创建一个workbook_open（）事件，并将你的OnTime代码复制粘贴到workbook_open事件> Save&关闭

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

现在，每次打开工作簿时，工作簿都应在8:47运行所需的代码。

Now, everytime you open the workbook, the workbook should run the desired code at 8:47.

转到任务计划程序，右键单击创建基本任务...。给任务一个名字，然后点击下一步。选择一个触发器（如果你在这里提到的是每日），然后点击下一步。设置时间和重复周期，然后单击下一步。在操作中，选择启动程序，然后单击下一步。在程序/脚本文本框中浏览您的Excel文件，然后单击下一步（将其他文本框留空）。点击完成。我刚刚在我的电脑上试过了，它的工作原理。

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.

从Windows帮助论坛

PS：确保在8:47之前设置调度程序打开excel文件（所以也许是8:46）。

PS: Make sure you set the scheduler to open the excel file BEFORE 8:47 (so maybe 8:46).

干杯，

kpark

Cheers,
kpark

编辑：尝试运行它，看看你的OnTime是否正常工作..

try running this to see if your OnTime is working..

Sub RunOnTime()
    Application.OnTime Now + TimeSerial(0, 0, 10), "theSub"
End Sub

这篇关于Excel OnTime事件计划程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！