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.

Solution

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.

Comments

[Steve]

Hello! Apparently when automating Excel via COM NumberFormat equals to NumberFormatLocal. If you try it in VBA of course it works. But try the same in Delphi (or possibly from C#) and you'll see what I'm talking about. I was hoping for a workaround...

[Stefan]

Ok, as I see it, you have to modify the locale in the call. As I don't know, which Delphi you are using, two links which might help you (I didn't try this): link Old Delphi without .NET link with .NET (C#, but I guess this would be the same in Delphi)

[Steve]

Thank you for the solution Stefan. The reason I don't want to use this, is that it requires putting some unknown assembly code to a modified ComObj. These days when 64-bit support arrived to Delphi and ARM is possibly coming, I want to avoid such moves. I've found another solution on a Russian site, you can see my edit. The only thing I still don't know is why does Excel put a "\" to my local currency format? Ie.: it looks like this: "# ##0,00\ [$EUR]" instead of "# ##0,00 [$EUR]" Any ideas?

[Stefan]

Isn't it the same in plain Excel? If I read [office.microsoft.com/en-us/excel-help/… (the office help) correctly, it does not matter if you use a blank or backslash blank. It is the same.

[Stefan]

The other point: At least for my example, there was no modification of the assembler code neccessary (different to the Russion solution). And I did'nt need to modify ComObj.pas directly, but of course, I had to copy some code. Though, I would never use such a solution (it was more some academic question: Can it be done?), due to the fact, that it will too easily cause (small) defects, if you forget somewhere to set the locale. Since this effects only how the numbers are displayed in some tables, I guess, it would be quite difficult to notice that in tests.

[Arioch 'The]

As a mater of generic question, should COM Invoke call specify LangID other than zero or zero is valid number ? In other words, is it Delphi breaking the rules or Excel ? after XE2 Update4 i would easily believe Delphi breaking Automation. And i wonder if someone did bugreport on this...