AddThis Social Bookmark Button

Listen Print Discuss

Performing Web Queries in Excel 2003
Pages: 1, 2

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