Kbase P4442: MS Excel's Text values are interpreted as Decimal by Progress
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
5/5/2011 |
|
Status: Verified
SYMPTOM(s):
MS Excel's Text values are interpreted as Decimal by Progress
This only occurs if the cell's content is all numeric digits
This does not occur if the cell's content contains non-numeric text
Returns 123456.000000000 instead of 123456
FACT(s) (Environment):
Microsoft Excel 97-2007
Windows
Progress 9.x
OpenEdge 10.x
CAUSE:
MS Excel is returning a VT_R8 PROPVARIANT type, which represents a 64-bit IEEE floating point value. Progress / OpenEdge cannot alter this behavior.
FIX:
As a workaround, convert the floating point value to an integer using the INTEGER function, and then to a character using the STRING function. The conversion will be similar to:
Y = STRING(INTEGER(ExcelCom:Range("A1"):value)).
For example:
DEFINE VARIABLE chExcel AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE ret AS CHARACTER NO-UNDO.
DEFINE VARIABLE errNum AS INTEGER NO-UNDO.
CREATE "Excel.Application" chExcel.
/* Open MS Excel worksheet */
chExcel:Workbooks:open("c:\test1.xls").
/* Read value of the first cell */
ret = STRING(INTEGER(chExcel:Range("A1"):value)) NO-ERROR.
/* If the value is a CHARACTER the error 76 will occur */
IF ERROR-STATUS:ERROR THEN DO:
ASSIGN
errNum = ERROR-STATUS:GET-NUMBER(1).
/* If error then do not use the INTEGER function */
IF errNum = 76 THEN
ret = chExcel:Range("A1"):VALUE.
END.
/* Display the value in an alert-box */
MESSAGE ret VIEW-AS ALERT-BOX.
chExcel:QUIT().
RELEASE OBJECT chExcel.