You can use the OnTime method to schedule a macro to run sometime in the future. You need to specify the date and time for the macro to run, and the name of the macro. If you use the Wait method of the Application object to pause a macro, all Excel activity, including manual interaction, is suspended. The advantage of OnTime is that it allows you to return to normal Excel interaction, including running other macros, while you wait for the scheduled macro to run.

Say you have an open workbook with links to Data.xls, which exists on your network server but is not currently open. At 3 p.m. you want to update the links to Data.xls. The following example schedules the RefreshData macro to run at 3 p.m., which is 15:00 hours using a 24-hour clock, on the current day. Date returns the current date, and the TimeSerial function is used to add the necessary time:

Sub RunOnTime()

Application.OnTime Date + TimeSerial(15, 0, 0),


End Sub

It is worth noting that if you attempt to run this macro when it is currently after 3 p.m., you will receive an error message because you cannot schedule a task to run in the past. If necessary, change the time to one in the future.

The following RefreshData macro updates the links to Data.xlsx that exist in ThisWorkbook using the UpdateLink method. ThisWorkbook is a convenient way to refer to the workbook containing the macro:

Sub RefreshData()

ThisWorkbook.UpdateLink Name:="C:\Data.xlsx", Type:=xlExcelLinks End Sub

If you want to keep refreshing the data on a regular basis, you can make the macro run itself as follows:

Dim mdteScheduledTime As Date

Sub RefreshData()

ThisWorkbook.UpdateLink Name:="C:\Data.

xlsx", Type:=


mdteScheduledTime = Now + TimeSerial(0,

1, 0)

Application.OnTime mdteScheduledTime, "


End Sub

Sub StopRefresh()

Application.OnTime mdteScheduledTime, "


, False

End Sub

Once you run RefreshData, it will keep scheduling itself to run every minute. In order to stop the macro, you need to know the scheduled time, so the module-level variable mdteScheduledTime is used to store the latest scheduled time. StopRefresh sets the fourth parameter of OnTime to False to cancel the scheduled run of RefreshData.

When you schedule a macro to run at a future time using the OnTime method, you must make sure that Excel keeps running in memory until the scheduled time occurs. It is not necessary to leave the workbook containing the OnTime macro open. Excel will open it, if it needs to.

The OnTime method is also useful when you want to introduce a delay in macro processing to allow an event to occur that is beyond your control. For example, you might want to send data to another application through a DDE link and wait for a response from that application before continuing with further processing. To do this, you would create two macros. The first macro sends the data and schedules the second macro (which processes the response) to run after sufficient time has passed. The second macro could keep running itself until it detected a change in the worksheet or the environment caused by the response from the external application.

0 0

Post a comment