Programming Excel with VBA and .NET

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.

For example, 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.[Symbol].Value
    qt.Refresh

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