The macro below updates the Web query,copies the data to a new row,and schedules an update for 15 seconds later:

Sub DebtClock()

Dim WSQ As Worksheet

Set WSQ = Worksheets("MyQuery")

1 Cell A2 of MyQuery contains a Web query to 1 WaitSec = 15

NameOfThisProcedure = "DebtClock" ' — End of Input Section

' Determine the next time this should run NextTime = Time + TimeSerial(0, 0, WaitSec)

' Schedule ThisProcedure to run then Application.OnTime EarliestTime:=NextTime,


1 Update Web query, capture data 1 Refresh the Web query

WSQ.Range("A2").QueryTable.Refresh BackgroundQuery:=False 1 Make sure the data is updated Application.Wait (Now + TimeValue("0:00:05"))

1 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) 'Freeze date & time

WSQ.Cells(NextRow, 2).Value = WSQ.Cells(NextRow, 2).Value End Sub

At the beginning of the period, I ran the macro and turned the class's attention to something else. After 30 minutes, I had an impressive table showing in real time how the national debt was climbing by a little over $1 million every minute (see Figure 14.8).

Figure 14.8

After several minutes, you have an impressive trend of real-time data.

A2 - fr ■J7.0D1 ,B1 6,278,2B9.95

0 0

Post a comment