Performing Web Queries in Excel 2003

Related Reading Excel 2003 Programming: A Developer's Notebook By Jeff Webb

Editor's note: It may not seem as though much has changed in Excel 2003, but if you're an Excel VBA programmer, you will want to know about some new programming features. Excel 2003 Programming: A Developer's Notebook will get you up to speed quickly on working with lists and XML data, securing Excel applications, using Visual Studio Tools for Office, collecting data with Infopath, and more--in the form of hands-on labs that address specific programming problems. This sample lab on performing web queries offers a glimpse at the sort of projects you'll find in the book, which is due for release in late August 2004.

Web queries are a quick way to import data from a web page into a worksheet using a QueryTable object.

How do I do that?

To perform a web query:

1. Choose Data --> Import External Data --> New Web Query. Excel displays the New Web Query dialog as shown in Figure 1.
2. Type the address of the web page you want to import data from in the Address bar and click Go to navigate to that page. It is usually easiest to find the page you want in your browser, then cut and paste that address into the New Web Query dialog box.
3. Excel places small yellow boxes next to the items you can import from the page. Click on the item or items you want to import and Excel changes the yellow box to a green check mark.
4. Click the Options button to set how Excel formats imported items. Formatting options are shown in Figure 2.

Figure 1. Use web queries to import data directly from a web page.

Figure 2. Set formatting options for the query.

5. Close the Options dialog box and click Import. Excel displays the Import dialog box as shown
   in Figure 3.

   Figure 3. Choose the destination for the imported data.

6. Click Properties to determine how the query is performed, such as how the data is refreshed. Figure 4 shows the query property settings.

   Figure 4. Use query properties to name the query, set how data is refreshed, and define how cells are inserted.

7. Close the Properties dialog and click OK to import the data. Figure 5 shows a real-time stock quote and quote history imported from the Yahoo web site. Yahoo is a good source for this type of web query because it is a free service and doesn't require you to register or sign in.

   Figure 5. Using a web query to get stock price data.

How does it work?

If you record the preceding web query, you'll get code that looks something like this:

With ActiveSheet.QueryTables.Add(Connection:= _
  "URL;http://finance.yahoo.com/q/ecn?s=SNDK", _
  Destination:=Range("C2"))
  .Name = "Real-Time Quote"
  .FieldNames = True
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .BackgroundQuery = True
  .RefreshStyle = xlOverwriteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .WebSelectionType = xlSpecifiedTables
  .WebFormatting = xlWebFormattingNone
  .WebTables = "22"
  .WebPreFormattedTextToColumns = True
  .WebConsecutiveDelimitersAsOne = True
  .WebSingleBlockTextImport = False
  .WebDisableDateRecognition = False
  .WebDisableRedirections = False
  .Refresh BackgroundQuery:=False
End With

With ActiveSheet.QueryTables.Add(Connection:= _
  "URL;http://finance.yahoo.com/q/
hp?a=01&b=5&c=2003&d=01&e=5&f=2004&g=d&s=sndk" _
  , Destination:=Range("A9"))
  .Name = "Price History"
  .FieldNames = True
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .BackgroundQuery = True
  .RefreshStyle = xlOverwriteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .WebSelectionType = xlSpecifiedTables
  .WebFormatting = xlWebFormattingNone
  .WebTables = "30"
  .WebPreFormattedTextToColumns = True
  .WebConsecutiveDelimitersAsOne = True
  .WebSingleBlockTextImport = False
  .WebDisableDateRecognition = False
  .WebDisableRedirections = False
  .Refresh BackgroundQuery:=False
End With

Some key properties and methods above shown in bold bear mention here:

• The Add method creates the query and adds it to the worksheet.
• The RefreshStyle property tells Excel to overwrite existing data rather than to insert new cells each time the query is refreshed.
• The WebTables property identifies which item from the page to import. Excel assigns a index to each item on the page and you can import one or more items or the entire page if WebSelectionType is set to xlEntirePage.
• The Refresh method imports the data onto the worksheet. Without this method, the query results are not displayed.

The query itself is consists of the Connection, WebTables, and formatting properties. If you save the web query to a query file (.iqy), the data looks like this:

WEB
1
http://finance.yahoo.com/q/
hp?a=01&b=5&c=2003&d=01&e=5&f=2004&g=d&s=sndk
Selection=30
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

When Excel updates a web query, a small, green globe is displayed in the status bar at the bottom of the screen, as shown in Figure 6. This symbol indicates that the query is being refreshed from the Internet.

Figure 6. Excel is refreshing the query from the Internet.

Jeff Webb is a SharePoint consultant and trainer who has written about computers and technology for 20 years. Among his published O'Reilly titles are Essential SharePoint, SharePoint Office Pocket Guide, Programming Excel with VBA and .NET, and Excel 2003 Programming: A Developer's Notebook. Jeff was an original member of Microsoft's Visual Basic team.

Return to WindowsDevCenter.com.

Copyright © 2009 O'Reilly Media, Inc.