VBA/Excel/Access/Word/Application/Application OnTime

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

   <source lang="vb">

    Dim mdteScheduledTime As Date
    Sub RefreshData()
        ThisWorkbook.UpdateLink Name:="C:\YourExcel2007File.xlsx", Type:= xlExcelLinks
        mdteScheduledTime = Now + TimeSerial(0, 1, 0)
        Application.OnTime mdteScheduledTime, "RefreshData"
    End Sub
    Sub StopRefresh()
        Application.OnTime mdteScheduledTime, "RefreshData",, False
    End Sub
</source>
   
  

Run on time

   <source lang="vb">

Sub RunOnTime()

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

End Sub

</source>
   
  

Scheduling a Macro to Run Every Two Minutes

   <source lang="vb">

Sub ScheduleAnything()

   WaitHours = 0
   WaitMin = 2
   WaitSec = 0
   NameOfThisProcedure = "ScheduleAnything"
   NameOfScheduledProc = "RemindMe"
   NextTime = Time + TimeSerial(WaitHours, WaitMin, WaitSec)
   Application.OnTime EarliestTime:=NextTime, Procedure:=NameOfThisProcedure
   Application.Run NameOfScheduledProc

End Sub Sub RemindMe()

   Application.Speech.Speak Text:="meeting."

End Sub

</source>
   
  

Set Refresh Data Timer

   <source lang="vb">

Sub RunOnTime()

  Application.OnTime (Date + TimeSerial(10, 47, 0)), "RefreshData1"

End Sub

</source>
   
  

The OnTime event

   <source lang="vb">

Sub SetAlarm()

   Application.OnTime 0.625, "DisplayAlarm"

End Sub Sub DisplayAlarm()

   Beep
   MsgBox "Wake up. It"s time for your afternoon break!"

End Sub

</source>
   
  

Use OnTime to trigger the refresh data

   <source lang="vb">

    Sub RunOnTime()
        Application.OnTime Date + TimeSerial(15, 0, 0), "RefreshData"
    End Sub
         
    Sub RefreshData()
        ThisWorkbook.UpdateLink Name:="C:\YourExcel2007File.xlsx", Type:=xlExcelLinks
    End Sub
</source>
   
  

Using Application.OnTime to Periodically Analyze Data

   <source lang="vb">

Sub ScheduleTheDay()

   Application.OnTime EarliestTime:=TimeValue("8:00 AM"), Procedure:=CaptureData
   Application.OnTime EarliestTime:=TimeValue("9:00 AM"), Procedure:=CaptureData

End Sub Sub CaptureData()

   Dim myWorksheet As Worksheet
   Dim NextRow As Long
   Set myWorksheet = Worksheets("Sheet1")
   myWorksheet.range("A2").QueryTable.Refresh BackgroundQuery:=False
   Application.Wait (Now + timeValue("0:00:10"))
   NextRow = myWorksheet.cells(65536, 1).End(xlUp).row + 1
   myWorksheet.range("A2:B2").copy myWorksheet.cells(NextRow, 1)

End Sub

</source>
   
  

writes the time to cell A1 and also programs another event five seconds later.

   <source lang="vb">

Dim NextTick As Date Sub UpdateClock()

   ThisWorkbook.Sheets(1).Range("A1") = Time
   NextTick = Now + TimeValue("00:00:05")
   Application.OnTime NextTick, "UpdateClock"

End Sub Sub StopClock()

   On Error Resume Next
   Application.OnTime NextTick, "UpdateClock", , False

End Sub

</source>