Microsoft Office Tutorials and References
In Depth Information
Using Web Queries
Web Queries were introduced in Excel 97 and have been enhanced with each subsequent
version of Excel. They allow 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 spe
One of the problems with Web Queries is that Excel uses the thousands and decimal separa
tors specified in the Windows Regional Settings when attempting to recognize numbers in
the page. If, for example, an exchange rate Web page is retrieved from one of many European
countries, the period is treated as a thousands separator, not a decimal separator, resulting in
exchange rates that are many times too large. Therefore, Web Queries couldn’t be reliably
used in versions prior to Excel 2002 in countries that used non-U.S. format decimal and
thousand separators. However, now there’s a workaround for this problem.
There are three properties to the Application object to temporarily override the settings used
when recognizing numbers. The following properties are available:
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. 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. To capture these events, class modules need to be set up.
The following procedure demonstrates how to retrieve the table of exchange rates using a
Web Query created within the procedure.
Note The RetrieveWebQueryData procedure imports the table of exchange rates into the
Excel workbook. If you don’t see the Exchange Rates in the workbook, verify which table you
need to import. The sidebar “That Was Table Number 2?”, on p. 531, explains this scenario.
With ActiveSheet.QueryTables.Add(Connection:= _
“URL;http://www.fms.tre as.gov/intn.html", Destination:=Range(“A1”))
.Name = “USD"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells