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 ','.

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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.
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.
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...
3

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

Improve this answer

2 Comments

That should do the trick. Excel 2007 probably determines the format based on the type of the value.
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.
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"
Improve this answer

2 Comments

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?
True, Standard is the new General

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.