Using Application.OnTime to Periodically Analyze Data : Application OnTime « Application « VBA / Excel / Access / Word






  1. VBA / Excel / Access / Word
  2. Application
  3. Application OnTime

Using Application.OnTime to Periodically Analyze Data

 
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








Related examples in the same category

1.Run on time
2.Set Refresh Data Timer
3.If you want to keep refreshing the data on a regular basis, you can make the macro run itself
4.The OnTime event
5.Scheduling a Macro to Run Every Two Minutes
6.Use OnTime to trigger the refresh data
7.writes the time to cell A1 and also programs another event five seconds later.