Using ApplicationOnTime to Periodically Analyze Data

VBA offers the .OnTime method for running any VBA procedure at a specific time of day or after a specific amount of time has passed.

You could write a macro that would capture data every hour throughout the day. This macro would have times hard-coded. The following code will theoretically capture data from a Web site every hour throughout the day:

Sub ScheduleTheDay()

Application.OnTime EarliestTime:=TimeValue("8:00 AM"), _

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

Procedure:=CaptureData Application.OnTime EarliestTime:=TimeValue("10:00 AM"), _

Procedure:=CaptureData Application.OnTime EarliestTime:=TimeValue("11:00 AM"), _

Procedure:=CaptureData Application.OnTime EarliestTime:=TimeValue("12:00 AM"), _

Procedure:=CaptureData Application.OnTime EarliestTime:=TimeValue("1:00 PM"), _

Procedure:=CaptureData Application.OnTime EarliestTime:=TimeValue("2:00 PM"), _

Procedure:=CaptureData Application.OnTime EarliestTime:=TimeValue("3:00 PM"), _

Procedure:=CaptureData Application.OnTime EarliestTime:=TimeValue("4:00 PM"), _

Procedure:=CaptureData Application.OnTime EarliestTime:=TimeValue("5:00 PM"), _ Procedure:=CaptureData

End Sub

Sub CaptureData()

Dim WSQ As Worksheet

Dim NextRow As Long

Set WSQ = Worksheets("MyQuery")

' Refresh the Web query

WSQ.Range("A2").QueryTable.Refresh BackgroundQuery:=False ' Make sure the data is updated Application.Wait (Now + TimeValue("0:00:10")) ' Copy the Web query results to a new row NextRow = WSQ.Range("A65536").End(xlUp).Row + 1 WSQ.Range("A2:B2").Copy WSQ.Cells(NextRow, 1) End Sub

0 0

Post a comment