Error in using ABAP OLE wrong cell value

[Former Member]

Dear All..

I'm facing a problem while using ABAP OLE..

the problem is the ABAP OLE writes the wrong value in Excel file

the value is 121.000 but it written in Excel file : 121

the weird thing is .. other value of  9.000.000 written correctly in Excel File .. and also lots of other values..

i've trace the value until it goes to the method of :

CALL METHOD OF h_excel 'Cells' = h_zl
     EXPORTING
       #1 = i_row
       #2 = i_col.
   SET PROPERTY OF h_zl 'Value' = i_value.
   GET PROPERTY OF h_zl 'Font' = h_f.
   IF i_bold = 'X'.
     SET PROPERTY OF h_f 'Bold' = 1.
   ENDIF.

   and the values of variable i_value is still correct which is : 121.000 but after writing in excel it becomes 121

   i believe it will become faulty at the specific amount with format of XXX.000  because if I changed the value to 121.001 it displays correctly in Excel file..

i_value is a pass value from variable ld_val with data type of is --> ld_val(20) type n.

is this are originally bugs from the OLE ? how to solved it ?

please advice ..

[former_member302911]

Hi Sasmito,

What's the type of i_value ?

I've tried this code:

DATA value TYPE i VALUE 120000.

SET PROPERTY OF lo_cell 'Value' = value.
CALL METHOD   OF lo_cell 'FONT' = lo_font.
SET PROPERTY OF lo_font 'Name' = 'Arial'.
SET PROPERTY OF lo_font 'Size' = 15.

EXCEL Cell = 120000

SET PROPERTY OF lo_cell 'Value' = '120.000'.
CALL METHOD   OF lo_cell 'FONT' = lo_font.
SET PROPERTY OF lo_font 'Name' = 'Arial'.
SET PROPERTY OF lo_font 'Size' = 15.

Excel Cell = 120

Regards,

Angelo.

[Former Member]

Hi Angelo..

let me added some of my codes :

DATA : ld_val(20) TYPE n.

WRITE value1 TO ld_val CURRENCY CURR.

CALL METHOD OF h_excel 'Cells' = h_zl
     EXPORTING
     #1 = i_row
     #2 = i_col.
   SET PROPERTY OF h_zl 'Value' = ld_val.
   GET PROPERTY OF h_zl 'Font' = h_f.
   IF i_bold = 'X'.
     SET PROPERTY OF h_f 'Bold' = 1.
   ENDIF.

is that clear enough ? and yes if we passed the amount of 120000 without any formatting it will display the correct value..

but i still dont get it .. why the amount of 9.000.000 displaying the correct value even with suitable formatting..

in your case :

SET PROPERTY OF lo_cell 'Value' = '9.000.000'.

CALL METHOD   OF lo_cell 'FONT' = lo_font.

SET PROPERTY OF lo_font 'Name' = 'Arial'.

SET PROPERTY OF lo_font 'Size' = 15.

Excel Cell = 9.000.000

thank you,

[former_member302911]

yes it is clear, but how excel manage the passed data is unclear.

However, to solve you can try this:

DATA : ld_val(20).

WRITE value1 TO ld_val CURRENCY CURR.
REPLACE ALL OCCURRENCES OF '.' IN ld_val WITH space.

CALL METHOD OF h_excel 'Cells' = h_zl
     EXPORTING
     #1 = i_row
     #2 = i_col.
   SET PROPERTY OF h_zl 'Value' = ld_val.
   GET PROPERTY OF h_zl 'Font' = h_f.
   IF i_bold = 'X'.
     SET PROPERTY OF h_f 'Bold' = 1.
   ENDIF.

Regards,

Angelo.

[Former Member]

Angelo..

thanks for your code..

I'm currently using some similar of your code since i don't find any alternative solutions..

thank you,

[Former Member]

Hi Sasmito,

Now you write your code as follows.

CALL METHOD OF h_excel 'Cells' = h_zl
     EXPORTING
       #1 = i_row
       #2 = i_col.
   SET PROPERTY OF h_zl 'Value' = i_value.

   SET PROPERTY OF h_zl 'NumberFormat' = '#,##0.00;-#,##0.00;'.
   GET PROPERTY OF h_zl 'Font' = h_f.
   IF i_bold = 'X'.
     SET PROPERTY OF h_f 'Bold' = 1.
   ENDIF.

I hope your problem is solve with this solutions.

Thanks,

Manob