If the data in a web query changes frequently, you may want to have Excel automatically update the information periodically. Since web queries already run asynchronously in the background, getting them to update periodically is a simple matter of setting a property:
Set qt = ws.QueryTables("Real-Time Quote") qt.RefreshPeriod = 1
qt.RefreshPeriod = 0
Performing web queries in the background can seem a little strange—particularly if they are set to refresh periodically. Most Excel actions are synchronous, and it might surprise a user to see Excel pause for a second, update some cells, and then continue on as if nothing happened. This can become a big problem if the source of the web query changes and causes the web query to fail—the user will see an error message periodically and may not know what to do or how to fix it (Figure 4-7).
Events are a big deal any time you use an object asynchronously. Query tables don’t automatically add their events to the worksheet’s event list the way command buttons and other controls do. You need to take special steps to hook up query table events.
To handle errors from asynchronous web queries, you must hook into the QueryTable events. You have to declare a QueryTable object variable using the
qualifier in order to trap its events.
can only be used in a class module or an Excel object module (such as the code module for a worksheet or workbook).
Display the code window for the worksheet by double-clicking on wsWebQuery in the Visual Studio Project Explorer.
Add the following declaration to the worksheet’s code module at the class level (outside of a procedure definition):
Dim WithEvents qt As QueryTable
Add the following code to disable/enable the command buttons and to get feedback from the user if an error occurs:
Private Sub qt_BeforeRefresh(Cancel As Boolean) ' Disable command button. cmdQuote.Enabled = False End Sub Private Sub qt_AfterRefresh(ByVal Success As Boolean) ' If update failed, get feedback. If Not Success Then If MsgBox("An error occurred getting web data. " & _ "Cancel future updates?", vbYesNo, "Web Query") = vbYes Then _ qt.RefreshPeriod = 0 End If ' Re-enable command button. cmdQuote.Enabled = True End Sub
Write code to initialize the QueryTable object and to begin updates. For example, the following procedure hooks an existing QueryTable up to the event handlers defined above and sets the stock symbol the query uses:
Private Sub cmdQuote_Click( ) ' Get the QueryTable and hook it to the event handler object. Set qt = ActiveSheet.QueryTables("Real-Time Quote") ' Set the query. qt.Connection = "URL;http://finance.yahoo.com/q/ecn?s=" & [Symbol].Value ' Set the refresh period and make sure it's done asynchronously. qt.RefreshPeriod = 1 qt.BackgroundQuery = True ' Refresh the data now. qt.Refresh End Sub
Now, the user can stop the automatic updates if the query fails.
Anticipating potential asynchronous collisions can be a little tricky. One general way to deal with these is to lock out other operations in the BeforeRefresh event and re-enable operations in the AfterRefresh event by enabling and disabling the command button as shown in Step 4. That prevents the user from changing a query while it is pending. Another way is to check the
property (shown earlier). A final solution is not to use asynchronous queries at all.
For example, the following code gets the price history for a stock. Since price history data isn’t very volatile, the code performs the query synchronously and waits for the result:
' Displays one year of the current symbol's price history. Private Sub cmdHistory_Click( ) Dim ws As Worksheet, qt2 As QueryTable, conn As String Set ws = ThisWorkbook.ActiveSheet ' Build query string. conn = "URL;http://chart.yahoo.com/d?" &_ YahooDates(Date - 365, Date) & ws.[Symbol].Value ' Get query Set qt2 = ws.QueryTables("Price History_1") ' Clear old history qt2.ResultRange.Clear ' Set connection property qt2.Connection = conn ' Make sure background queries are off. qt2.BackgroundQuery = False ' Refresh data qt2.Refresh End Sub ' Converts start and end dates to Yahoo query string for ' stock history. Function YahooDates(dtstart As Date, dtend As Date) As String ' Query sample string from Yahoo has this form: ' a=10&b=4&c=2003&d=1&e=5&f=2004&g=d&s=sndk Dim str As String str = "a=" & Month(dtstart) - 1 & "&b=" & Day(dtstart) & _ "&c=" & Year(dtstart) & "&d=" & Month(dtend) - 1 & _ "&e=" & Day(dtend) & "&f=" & Year(dtend) & "&g=d&s=" Debug.Print str YahooDates = str End Function