Problem:

I was downloading lately a lot of financial data (annual statements and so on) automatically into Excel spreadsheets. Direct web queries and VBA could handle the task quite sufficiently, but I had a lot of problems with number formatting. The decimal and thousands separators differed from one site to another (and locally too) so getting it all compatible wasn’t that easy. A no-brainer solution was to just stick to one formatting scheme (used by the main data source) and use it in all places (counting in the need to convert other sources to the same format). As I don’t like being forced to do things the way I don’t like them to be done, this was no real solution in my case.

Solution:

Fortunately, though after some misleading advices resulting from Google search, I found the ultimate resolution in Microsoft Developer Network Library: Different Ways of Using Web Queries in Microsoft Office Excel 2003. The interesting part can be found at the very end:

In Excel 2002 and later, three properties were added to the Application object to temporarily override the settings used when recognizing numbers:

• Application.DecimalSeparator
  Character used for the decimal separator
• Application.ThousandsSeparator
  Character used for the thousands separator
• Application.UseSystemSeparators
  Character that specifies whether to use the separator from Windows or Excel

So the way to go is to use this kind of VBA construct:

Dim currentDecimalSeparator As String
Dim currentThousandsSeparator As String
Dim currentUseSystemSeparators As Boolean

With Application
   ' Store current number format settings
   currentDecimalSeparator = .DecimalSeparator
   currentThousandsSeparator = .ThousandsSeparator
   currentUseSystemSeparators = .UseSystemSeparators

   ' Set separators for the web query to match the source (website)
   .DecimalSeparator = "."
   .ThousandsSeparator = ","
   .UseSystemSeparators = False

   ' Do the processing (all queries and stuff)
   ' [...]

   ' Reset number format settings
   .DecimalSeparator = currentDecimalSeparator
   .ThousandsSeparator = currentThousandsSeparator
   .UseSystemSeparators = currentUseSystemSeparators
End With

This way automatic scripts can handle various locales and end up with a user-specific number formatting of resulting data.