代码以时间间隔运行宏？ [英] Code to run macro on time interval?

问题描述

如何将宏设置为在特定时间运行，然后再设置间隔？我希望在每个小时的顶部运行，所以我想在07:00 AM开始，例如，然后在我想要再次运行后的每个小时。这是代码：

  Sub Refresh_All（）
'
'Refresh_All Macro 
' 
'键盘快捷键：Ctrl + Y 
'
 ChDirQ：\ Quality Control
 Workbooks.Open文件名：= _ 
Q：\Quality Control\Internal Failure Log  -  Variable Month.xlsm
 Dim endTime As Date 
 endTime = DateAdd（s，10，Now（））
 Do While Now（）< endTime 
 DoEvents 
循环
 ActiveWorkbook.RefreshAll 
 endTime = DateAdd（s，10，Now（））
 Do While Now（）< endTime 
 DoEvents 
循环
 ActiveWorkbook.Save 
 endTime = DateAdd（s，5，Now（））
 Do While Now（） endTime 
 DoEvents 
循环
 ActiveWindow.Close 
 ChDirQ：\Reports
 Workbooks.Open文件名：= _ 
Q：\报告\Finished-Transfer Report-variable month.xlsm
 endTime = DateAdd（s，10，Now（））
 Do While Now（）< endTime 
 DoEvents 
循环
 ActiveWorkbook.RefreshAll 
 endTime = DateAdd（s，10，Now（））
 Do While Now（）< endTime 
 DoEvents 
循环
 ActiveWorkbook.Save 
 endTime = DateAdd（s，5，Now（））
 Do While Now（） endTime 
 DoEvents 
循环
 ActiveWindow.Close 
 ActiveWorkbook.RefreshAll 
 endTime = DateAdd（s，10，Now（））
立即执行（） endTime 
 DoEvents 
循环
 ActiveWorkbook.Save 
 End Sub 
  

解决方案

Lumigraphics 提出了关于内存使用的一个好点，应该考虑。为了学习，我将介绍一个不需要任务调度程序并可以在显示工作簿中完全工作的替代解决方案。

在VB编辑器中，添加新的子程序

  Public Sub RefreshDataEachHour（）
 
 Application.OnTime Now + TimeValue（01:00： 00），Refresh_All
 
 End Sub 
  

并添加以下内容一行到你的 Refresh_All 例程的结尾

调用RefreshDataEachHour

当 Refresh_All 例程首次运行时，将会调用此新过程，然后等待一小时，然后调用 Refresh_All 。再次在 Refresh_All 的结尾，它返回到这个存储过程，这将再次等待一个小时，然后调用 Refresh_All 等等。直到退出Excel应用程序为止。

以下这部分超出了您的问题的范围，但我觉得很重要。

基本上， Application.OnTime 安排一个任务在将来的某个时间运行。您可能希望在某个时间点结束此计划任务。要这样做，您必须调用 Application.OnTime 方法与参数 Schedule：= False ，您必须传入您的第一个 Application.OnTime 调用程序的确切时间。

为了处理这种情况，请声明一个全局变量（在所有子例程之外），并将该变量传递到将取消任务的新子例程。

例如，您可以声明

公开运行作为日期

然后，您将修改上述过程如下：

  Public Sub RefreshDataEachHour（）
 
 RunWhen = Now + TimeValue（01:00： 00）
 Application.OnTime EarliestTime：= RunWhen，Procedure：=Refresh_All，Schedule：= True 
 
 End Sub 
  

然后，您可以添加另一个处理任务取消的子例程，如下所示：

  Public Sub CancelRefresh（）
 
应用程序lication.OnTime EarliestTime：= RunWhen，Procedure：=Refresh_All，Schedule：= False 
 
 End Sub 
  

每当你使用调用CancelRefresh ，那么应该将你的过程从队列中删除。

How can I set a macro to run at a specific time and then at set intervals afterwards? I would like it to run at the top of each hour so I would like to start it at 07:00AM for example and then every hour after I want it to run again. Here is the code:

Sub Refresh_All()
'
' Refresh_All Macro
'
' Keyboard Shortcut: Ctrl+Y
'
    ChDir "Q:\Quality Control"
    Workbooks.Open Filename:= _
        "Q:\Quality Control\Internal Failure Log - Variable Month.xlsm"
    Dim endTime As Date
    endTime = DateAdd("s", 10, Now())
    Do While Now() < endTime
DoEvents
    Loop
    ActiveWorkbook.RefreshAll
       endTime = DateAdd("s", 10, Now())
    Do While Now() < endTime
        DoEvents
    Loop
ActiveWorkbook.Save
    endTime = DateAdd("s", 5, Now())
    Do While Now() < endTime
        DoEvents
    Loop
    ActiveWindow.Close
    ChDir "Q:\Reports"
    Workbooks.Open Filename:= _
        "Q:\Reports\Finished-Transfer Report-variable month.xlsm"
        endTime = DateAdd("s", 10, Now())
    Do While Now() < endTime
        DoEvents
    Loop
ActiveWorkbook.RefreshAll
     endTime = DateAdd("s", 10, Now())
    Do While Now() < endTime
        DoEvents
    Loop
ActiveWorkbook.Save
        endTime = DateAdd("s", 5, Now())
    Do While Now() < endTime
        DoEvents
    Loop
ActiveWindow.Close
    ActiveWorkbook.RefreshAll
        endTime = DateAdd("s", 10, Now())
    Do While Now() < endTime
        DoEvents
    Loop
ActiveWorkbook.Save
End Sub

解决方案

Lumigraphics raises a good point about memory usage, which should be considered. In the interest of learning I will present an alternate solution that doesn't require task scheduler and will work completely within the display workbook.

In the VB Editor, add this new subroutine

Public Sub RefreshDataEachHour()

    Application.OnTime Now + TimeValue("01:00:00"), "Refresh_All"

End Sub

And add the following line to the end of your Refresh_All routine

Call RefreshDataEachHour

When the Refresh_All routine is run for the first time it will call this new procedure, which will then wait an hour and then call Refresh_All. Again at the end of Refresh_All it returns to this stored procedure, which will wait an hour again, then call Refresh_All, etc. This will repeat until the Excel application is exited.

This part below is beyond the scope of your question, but I feel it important to mention anyways.

Basically, Application.OnTime schedules a task to run at some point in the future. You may want to end this scheduled task at some point in time. To do so you must call the Application.OnTime method with an argument Schedule:=False and you must pass in the exact time that you scheduled the procedure with your first Application.OnTime call.

To handle this case, declare a global variable (outside of all of your subroutines) and pass that variable into a new subroutine that will cancel the task.

For example, you can declare

Public RunWhen As Date

Then you would modify the above procedure as follows:

Public Sub RefreshDataEachHour()

    RunWhen = Now + TimeValue("01:00:00")
    Application.OnTime EarliestTime:=RunWhen,Procedure:="Refresh_All",Schedule:=True

End Sub

Then you can add another subroutine that would handle the canceling of the task, like so:

Public Sub CancelRefresh()

    Application.OnTime EarliestTime:=RunWhen, Procedure:="Refresh_All", Schedule:=False

End Sub

Whenever you use Call CancelRefresh that should remove your procedure from the queue.

这篇关于代码以时间间隔运行宏？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！