The number in this cell is formatted as text or preceded by an apostrophe" problem in Excel

[its_faiz]

Dear Experts,

We have developed an application in VB6. This application exports some
results to Excel. The Excel displays error "The number in this cell is
formatted as text or preceded by an apostrophe". An exclamation sign
appears with the cell offers "Convert to Number" option. We have
controlled all this through PasteSpecial VBA code within our
application.

xlBook.ActiveSheet.Range("Z7").Copy
xlBook.ActiveSheet.Range("A1:X10").PasteSpecial Paste:=xlPasteValues,
Operation:=xlAdd, SkipBlanks:=False, Transpose:=False

This code work fine if we use dot (.) as decimal separator. However for
another locale setting where comma (,) is used as a decimal separator,
the above block of code does not convert the values intu number (in
Excel). Can anyone help me out.

Any help will be highly appreciated.

Thanks & Regards,

Faiz

 

[Lee Peedin]

Dear Experts,

We have developed an application in VB6. This application exports some
results to Excel. The Excel displays error "The number in this cell is
formatted as text or preceded by an apostrophe". An exclamation sign
appears with the cell offers "Convert to Number" option. We have
controlled all this through PasteSpecial VBA code within our
application.

xlBook.ActiveSheet.Range("Z7").Copy
xlBook.ActiveSheet.Range("A1:X10").PasteSpecial Paste:=xlPasteValues,
Operation:=xlAdd, SkipBlanks:=False, Transpose:=False

This code work fine if we use dot (.) as decimal separator. However for
another locale setting where comma (,) is used as a decimal separator,
the above block of code does not convert the values intu number (in
Excel). Can anyone help me out.

Any help will be highly appreciated.

Thanks & Regards,

Faiz

Well I'm no expert by any means, but one might assume that Excel would
honor the locale settings of its host computer. If this is in fact
the case, could you not use the GetLocaleInfo API to determine what
the seperator was and adjust your cell data accordingly?

Lee

 

[Larry Serflaten]

We have developed an application in VB6. This application exports some
results to Excel. The Excel displays error "The number in this cell is
formatted as text or preceded by an apostrophe".

Just as a guess, have you tried preceeding the cell contents with an = sign?

My guess is that if it thinks its a function, perhaps it will do the right thing
with your number....

LFS