topic Re: Error in using ABAP OLE wrong cell value in Application Development and Automation Discussions

Error in using ABAP OLE wrong cell value

Former Member — Fri, 21 Mar 2014 11:12:59 GMT

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

Re: Error in using ABAP OLE wrong cell value

former_member302911 — Fri, 21 Mar 2014 11:30:45 GMT

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.

Re: Error in using ABAP OLE wrong cell value

Former Member — Fri, 21 Mar 2014 11:49:21 GMT

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,

Re: Error in using ABAP OLE wrong cell value

former_member302911 — Fri, 21 Mar 2014 12:06:30 GMT

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.

Re: Error in using ABAP OLE wrong cell value

Former Member — Mon, 24 Mar 2014 12:13:07 GMT

Angelo..

thanks for your code..

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

thank you,

Re: Error in using ABAP OLE wrong cell value

Former Member — Wed, 02 Apr 2014 11:26:20 GMT

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