How to correctly set NumberFormat property when automating different localized versions of Excel

Question

I've ran into the following problem:

When automating Excel via OLE from my Delphi program and trying to set a cell's NumberFormat property, Excel is expecting the format string in a localized format.

Normally, when checking the formatting by recording a macro in Excel, Excel is expecting it like this: Cells(1, 2).NumberFormat = "#,##0.00"

That means the thousands separator is "," and the decimal separator is ".".

In reality, I'm using a localized version of Excel. In my locale, the thousands separator is " " and the decimal separator is ",".

So whenever setting the NumberFormat from my Delphi program I need specify it like "# ##0,00".

My question is: Obviously, if I hardcode these values in my program there is going to be an exception when my program is used with an English or another differently localized version of Excel. Is there a "universal" way to set the NumberFormat property? (using the default English locale?)

Thanks!

Update: I've found a more elegant way to do it on this page: http://www.delphikingdom.com/asp/viewitem.asp?catalogid=920&mode=print It's in Russian (which I don't speak too) but you can easily understand the code.

Answer 1

In Excel you have two Fields:

• NumberFormat

• NumberFormatLocal

NumberFormat takes the format always locale invariant in the american standard and NumberFormatLocal expects the format with the set locale.

For example

Sub test()
    Dim r As Range
    Set r = ActiveWorkbook.ActiveSheet.Range("$A$1")
    r.NumberFormat = "#,##0.00"
    Set r = ActiveWorkbook.ActiveSheet.Range("$A$2")
    r.NumberFormat = "#.##0,00"
    Set r = ActiveWorkbook.ActiveSheet.Range("$A$3")
    r.NumberFormatLocal = "#,##0.00"
    Set r = ActiveWorkbook.ActiveSheet.Range("$A$4")
    r.NumberFormatLocal = "#.##0,00"       
End Sub

With german settings (decimal sep: , and thousand sep: .) gives you correct formatted numbers for $A$1 and $A$4. You can test it, if you change your regional settings in windows to anything you like and try, if your formatting is working.

Assuming you use Delphi 5 and have code to start Excel like this (and have access to ComObj.pas):

var
  oXL, oWB, oSheet : Variant;
 LocaleId : Integer;
begin
 oXL := CreateOleObject('Excel.Application');
 oXL.Visible := True;
 oWB := oXL.Workbooks.Add;
 oSheet := oWB.ActiveSheet;
 oSheet.Range['$A$1'].NumberFormatLocal := '#.##0,00';
 oSheet.Range['$A$2'].NumberFormatLocal := '#,##0.00';
 LocaleID:= DispCallLocaleID($0409);
 try
    oSheet.Range['$A$3'].NumberFormat := '#.##0,00';
    oSheet.Range['$A$4'].NumberFormat := '#,##0.00';
 finally
    DispCallLocaleId( LocaleId);
 end;
end;

then by default every call goes through ComObj.VarDispInvoke which calls ComObj.DispatchInvoke. There you find the call to Dispatch.Invoke which gets as third parameter the lcid. This is set to 0. You can use the technique shown in the first link in the comment to this, to create your own unit and copy all code from ComObj to your own unit (or modify ComObj directly). Just don't forget to set the VarDispProc variable in the initialization of the unit. The last part seems not work in all cases (probably depends on the order of the modules), but you can set the variable in your code:

 VarDispProc := @VarDispInvoke;

where you must place VarDispInvoke into the interface section of your ComObj copy module. The code of the first link does not work directly as it modifies a different method which is not called in the above Delphi sample.
And it is enough to change the locale for the numberformat call (to avoid side effects).
The above example together with the described modifications works for my german excel correct. Without the modification or the call to DispCallLocaleId I see the same problem as you describe.