Using Web Queries

Web Queries were introduced in Excel 97 and have been enhanced in each subsequent version of Excel. They enable you to retrieve a single table of information from a web page, with options to automatically refresh the data each time the workbook is opened, or at frequent intervals.

One of the problems with Web Queries is that Excel uses the thousands and decimal separators specified in the Windows Regional Settings when attempting to recognize numbers in the page. If the exchange rate web page were retrieved in many European countries, the period would be treated as a thousands separator, not a decimal separator, resulting in exchange rates that are many times too large. In Excel 2002, Microsoft added three properties to the Application object to temporarily override the settings used when recognizing numbers:

□ Application.DecimalSeparator — The character to use for the decimal separator

□ Application.ThousandsSeparator — The same for the thousands separator

□ Application.UseSystemSeparators — Whether to use the Windows separators or Excel's

Using these properties, you can set Excel's separators to match those on the web page, perform the query, and then set them back again. Web Queries could not be used reliably in versions prior to Excel 2002 in countries that used non-U.S. decimal and thousands separators. If you want to use the Web Query's automatic refreshing options, you have to set these separators in the BeforeRefresh event, and set them back in the AfterRefresh event. This requires advanced VBA techniques, using class modules to trap events, as discussed in Chapter 16.

In this case, you can retrieve just the table of exchange rates, using the following code to create and execute a new Web Query. In practice, it's easiest to use the macro recorder to ensure the selections are correct:

'Retrieve USD exchange rates using a Web Query Sub GetRatesWithWebQuery()

Dim oBk As Workbook Dim oQT As QueryTable

'Store the current settings of Excel's number formatting Dim sDecimal As String Dim sThousand As String Dim bUseSystem As Boolean

'Create a new workbook Set oBk = Workbooks.Add

'Create a query table to download USD rates With oBk.Worksheets(l)

Connection:="URL;http://www.x-rates.com/d/USD/table.html", Destination:=.Range("A1"))

End With

'Set the QueryTable's properties With oQT

'State that we're selecting a specific table .WebSelectionType = xlSpecifiedTables

'Import the 14th table on the page .WebTables = "14"

'Ignore the web page's formatting .WebFormatting = xlWebFormattingNone

'Do not try to recognize dates .WebDisableDateRecognition = True

'Don't automatically refresh the query each time the file is opened .RefreshOnFileOpen = False

'Waiting for the query to complete before continuing .BackgroundQuery = True

'Save the query data with the workbook .SaveData = True

'Adjust column widths to autofit new data

.AdjustColumnWidth = True End With

With Application

'Remember Excel's current number format settings sDecimal = .DecimalSeparator sThousand = .ThousandsSeparator bUseSystem = .UseSystemSeparators

'Set Excel's separators to match those of the web site .DecimalSeparator = "." .ThousandsSeparator = "," .UseSystemSeparators = True

'Ignore any errors raised by the query failing On Error Resume Next

'Perform the query, waiting for it to complete oQT.Refresh BackgroundQuery:=False

'Reset Excel's number format settings .DecimalSeparator = sDecimal .ThousandsSeparator = sThousand .UseSystemSeparators = bUseSystem End With

End Sub

The .WebTables = "14" line in this example tells Excel that you want the 14th table on the page. Literally, this is the 14th occurrence of a <TABLE> tag in the source HTML for the page.

0 0

Post a comment