Skip to Main Content
Excel 2003 Programming: A Developer's Notebook
book

Excel 2003 Programming: A Developer's Notebook

by Jeff Webb
August 2004
Intermediate to advanced content levelIntermediate to advanced
312 pages
8h 30m
English
O'Reilly Media, Inc.
Content preview from Excel 2003 Programming: A Developer's Notebook

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.

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

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham
Excel® 2007 VBA Programmer's Reference

Excel® 2007 VBA Programmer's Reference

John Green, Stephen Bullen, Rob Bovey, Michael Alexander
Office 2003 XML

Office 2003 XML

Simon St. Laurent, Mary McRae, Evan Lenz

Publisher Resources

ISBN: 0596007671Supplemental ContentCatalog PageErrata