Excel 2003 Programming: A Developer's Notebook

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 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).

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

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:

1. Display the code window for the worksheet by double-clicking on wsWebQuery in the Visual Studio Project Explorer.

2. Add the following declaration to the worksheet’s code module at the class level (outside of a procedure definition):

      Dim WithEvents qt As QueryTable

3. 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.

4. 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

5. 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.

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.