Section 4.3. Perform Periodic Updates | Excel 2003 Programming: A Developers Notebook (Developers Notebook)

4.3. Perform Periodic Updates

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

Now, the query will update every minute. To turn off the background query, set the RefreshPeriod to 0:

    qt.RefreshPeriod = 0

Interestingly, the BackgroundQuery property can be False and you can still perform periodic queries. In that case, the Excel user interface pauses periodically whenever the query is being refreshed.

Performing web queries in the background can seem a little strangeparticularly 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 failthe user will see an error message periodically and may not know what to do or how to fix it (Figure 4-7).

Note: 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.

Figure 4-7. Failed web queries may display errors asynchronously

4.3.1. How to do it

To handle errors from asynchronous web queries, you must hook into the QueryTable events. You have to declare a QueryTable object variable using the WithEvents qualifier in order to trap its events. WithEvents can only be used in a class module or an Excel object module (such as the code module for a worksheet or workbook).

For example, to handle asynchronous events for a QueryTable in the wsWebQuery worksheet module, follow these steps:

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
```
Select the qt object in the object list at the top of the code window, and then select AfterRefresh from the event list to create empty event procedures.

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.

: Strange but true

When working with asynchronous events in Excel, the event may run while you are editing it in Visual Basic. Often this will result in a runtime error because you haven't completed the code you were in the process of writing. It is a good idea to stop periodic updates while working on query table event code. You can do this by setting the query table's RefreshPeriod property to 0 in the Immediate window.

4.3.2. How it works

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 Refreshing 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

When you run the preceding code, Excel changes the mouse pointer to the wait symbol and won't accept user actions till the query returns. This provides a much simpler logical path for programming.