0

I'm sending a number to Excel 2007 as a string (Cell.Value := '2,5') using late binding. The actual code is more like:

var CellVal: OLEVariant;
...
CellVal := FloatToStr(2.5);  // Regionally formatted.
Cell.Value := CellVal;

On my Excel 97 version, this value will be formatted as "General" by default and will be seen as a number. A customer with Excel 2007 ends up with the cell formatted as "Standard" and Excel appears to see it as a string (it's not right aligned.) Note that I am using the regional settings to format the number and that Excel appears to be using the default regional settings as well.

If the customer just types 2,5 into a cell it accepts it as a number and if he does a copy of the string '2,5' from the clipboard into a cell, it also gets accepted as a number. Does anyone know why the string value sent though the automation interface to Excel ends up as a non-number?

Thanks for any suggestions! Edited to specify the regional decimal separator for the customer is ','.

MarkF
  • 1,616
  • 1
  • 17
  • 27

3 Answers3

3

Probably because you give it a string. Have you tried passing it the float value directly?

Ondrej Kelle
  • 36,941
  • 2
  • 65
  • 128
  • That should do the trick. Excel 2007 probably determines the format based on the type of the value. – The_Fox Nov 11 '09 at 19:02
  • Yes, it works if I pass the value as a float. However I'm really trying to find out why using a string doesn't work. I have a situation where I'd prefer to pass it as a string. – MarkF Nov 11 '09 at 19:50
3

Since you cannot format comments:

I just did a little test and Excel doesn't want a regional formatted float value as string, it just want a dot as decimal separator. 

procedure TForm1.Button1Click(Sender: TObject);
var
  App: Variant;
  Workbook: Variant;
  Worksheet: Variant;
  DoubleValue: Double;
begin
  App := CreateOleObject('Excel.Application');
  Workbook := App.Workbooks.Add;
  Worksheet := Workbook.ActiveSheet;
  DoubleValue := 1.2;
  Worksheet.Range['A1'].Value := DoubleValue; //DoubleValue is a double, excel recognizes a double
  Worksheet.Range['A2'].Value := '1.2'; //excel recognizes a double
  Worksheet.Range['A3'].Value := '1,2'; //excel recognizes a string
  Worksheet.Range['A4'].Value := FloatToStr(1.2); //excel recognizes a string
  App.Visible := True;
end;

Keep in mind that I hava a comma as decimal separator.

The_Fox
  • 6,992
  • 2
  • 43
  • 69
  • Thanks for verifying! What version of Excel are you using? My Excel 97 does handle regional strings properly it seems. I'm wondering if this behavior is new to Excel 2007. – MarkF Nov 12 '09 at 03:41
  • 1
    It's certainly true to say that often, Office automation requires values to be passed in using US settings and then Excel should localise it. I do extensive automation with Outlook and I have to pass dates in US date format, which are then correctly localised to my regional settings by Outlook/Exchange. – J__ Nov 12 '09 at 08:52
  • Thanks all for the comments. It seems that (at least in Excel's case) this changed in newer versions. This seems like a major flaw or bug, but I guess I'll just have to live with it. The date thing is a real problem since it will misinterpret dd/mm as mm/dd if the first part is less than 13. Ouch... – MarkF Nov 12 '09 at 14:30
0

Can't explain why the behaviour is different but it would appear to be down to how Excel 2007 interprets the incoming value.

How about setting the format of the cell in code?

Worksheets("Sheet1").Range("A17").NumberFormat = "General"
J__
  • 3,777
  • 1
  • 23
  • 31
  • Thanks. I don't have Excel 2007 here, but I think that "Standard" is the new "General". I could be wrong about that however! Can anyone with Excel 2007 comment on that? – MarkF Nov 11 '09 at 19:51
  • True, Standard is the new General – The_Fox Nov 12 '09 at 07:42