Web Queries

Although the concept behind Web Queries is an excellent one, they have been implemented with complete disregard for international issues. When the text of the web page is parsed by Excel, all the numbers and dates are interpreted according to your Windows Regional Settings. This means that if a European web page is opened in the U.S., or a U.S. page is opened in Europe, it is likely that the numbers will be wrong. For example, if the web page contains the text 1.1, it will appear as January 1 on a computer running Norwegian Windows.

The WebDisableDateRecognition option for the QueryTable can be used to prevent numbers from being recognized as dates. Setting Excel's override number and decimal separators can ensure that numbers are recognized correctly, if the web page is displayed in a known format.

Web Queries must be used with great care in a multinational application, using the following approach:

□ Set Application.UseSystemSeparators to False.

□ Set Application.DecimalSeparator and Application.ThousandsSeparator to those used on the web page.

□ Perform the query, ensuring WebDisableDateRecognition is set to True.

□ Reset Application.DecimalSeparator, Application.ThousandsSeparator, and Application.UseSystemSeparators to their original values.

0 0

Post a comment