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