Code to run macro on time interval?

Question

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

Solution 1

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.

Solution 2

Windows has a built-in Task Scheduler. Put your macro into a workbook and set it to run on the WorkBook.Open event. https://msdn.microsoft.com/en-us/library/office/ff196215.aspx

Set the Task Scheduler to open that Excel file every hour, run the macro, and have it close the file at the end. This cuts down on memory usage vs keeping Excel running. Task Scheduler also has the ability (for example) to send an email, so you could set it to email you when the task ran each hour.

Comments

[Dennis]

I like this idea although I'm not sure how to refresh the display worksheet that is already open once the other two have been updated and closed. Does it need to be or since it is already open will the data live refresh? The display worksheet is just simple links. a1 = a1 (workbook A) a2= a2 (workbook B). The display worksheet is open at all times. I could have task scheduler open the macro book, the macro will run on open, update the other two books and close them leaving the display workbook the only one open.