ExcelBanter - View Single Post - The number in this cell is formatted as text or preceded by an apostrophe" problem in Excel

Lee Peedin

On 13 Jul 2006 05:07:17 -0700, wrote:

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