Section 4.2. Modify a Web Query


4.2. Modify a Web Query

You can modify a web query by right-clicking on the query and selecting Edit Query. In many cases, however, you'll want a more automated approach. For example, you may want to let the user change the stock symbol in the previous sample. To do that, use code to:

  1. Change the Connection property of the query.

  2. Refresh the query.


Note: Recording code is a great way to learn how Excel does things, but it only takes you so far. You can modify recorded queries to change the query string dynamically based on user input.

4.2.1. How to do it

The following code allows the user to enter a stock symbol in a named range on the worksheet to get current and historical price data for that stock:

    Dim ws As Worksheet, qt As QueryTable    Set ws = ThisWorkbook.Sheets("Web Query")    Set qt = ws.QueryTables("Real-Time Quote")    qt.Connection = "URL;http://finance.yahoo.com/q/ecn?s=" & _      ws.Range("Symbol").Value    qt.Refresh    Set qt = ws.QueryTables("Price History")    qt.Connection = "URL;http://finance.yahoo.com/q/hp?a=01&b=5&c=2003& d=01&e=5&f=2004                               &g=d&s=" & _      ws.Range("Symbol").Value    qt.Refresh

4.2.2. How it works

If you run the preceding code, you may notice that the query is not updated right away. By default, web queries are done in the background asynchronously. This avoids tying up Excel while the web site responds to the query, but it can cause an error if you refresh the query again before the first request has had a chance to respond. You can avoid this by not performing the query in the background. For example, the following code turns off asynchronous queries, waiting for a response before executing the next line:

    qt.BackgroundQuery = False    qt.Refresh 

Or, more simply:

    qt.Refresh False

This causes Excel to wait while the query completes. During this time, the user can't edit cells or perform other tasks. If this is too much of a burden, use the QueryTable object's Refreshing property to avoid asynchronous collisions:

    Set qt = ws.QueryTables("Real-Time Quote")    If Not qt.Refreshing Then        qt.Connection = "URL;http://finance.yahoo.com/q/ecn?s=" & _           ws.[Symbol].Value        qt.Refresh    Else        MsgBox "Similar query is pending, please wait a second and try again."    End If

The preceding code checks if the web query is already executing before calling Refresh . If a previous query is still executing, the user is told to try again later. Notice that this code checks the status of a query performed by a single query table. Other, different query tables may have pending results without causing a collisionyou only need to check the Refreshing property of the target query table before attempting to change or refresh a query.



    Excel 2003 Programming. A Developer's Notebook
    Excel 2003 Programming: A Developers Notebook (Developers Notebook)
    ISBN: 0596007671
    EAN: 2147483647
    Year: 2004
    Pages: 133
    Authors: Jeff Webb

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net