ABAP OLE Objects

[Former Member]

Hi ,

I have used OLE Objects to move data from 5 internal tables to 5 sheets in a single XL file , but i am not able to get 4th and 5th sheet newly created , instead they are getting over writed in the third sheet itself.

so how to resolve this.

help reg this.

[RaymondGiuseppi]

I suppose you did not create the sheets, but your Excel seems to be configured to create three sheets when a new file is created

For sample look at thread

Regards

[Former Member]

Hi,

I think you have given the third sheet name for fourth and fifth.

call method of application 'Worksheets' = sheet

exporting

#1 = 1.

set property of sheet 'Name' = sheet_name.

call method of sheet 'Activate'.

OR

you might have missed to add sheet.

CALL METHOD OF application 'Sheets' = sheets.

CALL METHOD OF sheets 'Add'.

Run my program:

REPORT ZP_EXCEL .

&----

---

*& Report ZP_EXCEL *

&----

---

include ole2incl.

data: application type ole2_object,

workbook type ole2_object,

sheet type ole2_object,

cells type ole2_object,

sheets TYPE ole2_object.

constants: row_max type i value 256.

data index type i.

data : det type ref to CL_ABAP_structDESCR,

wa like line of det->components.

data: begin of i_finaltab occurs 0,

Col1 type i,

col2 type i,

col3 type i,

col4 type i,

end of i_finaltab.

data : count type i,

v_excel_count(3), " type i value 1,

sheet_name(15) .

************************************************************************

*START-OF-SELECTION

start-of-selection.

do 100 times.

count = count + 1.

i_finaltab-col2 = 5 * count.

i_finaltab-col3 = 10 * count.

i_finaltab-col4 = 20 * count.

move : count to i_finaltab-col1.

append i_finaltab.

clear i_finaltab.

enddo.

clear count.

create object application 'excel.application'.

set property of application 'visible' = 1.

call method of application 'Workbooks' = workbook.

call method of workbook 'Add'.

• Create first Excel Sheet

call method of application 'Worksheets' = sheet

exporting

#1 = 1.

call method of sheet 'Activate'.

set property of sheet 'Name' = 'EXCEL0'.

count = 1.

index = row_max * ( count - 1 ) + 1.

perform header_details.

DATA FNAME(60) VALUE '/data/sapdata/mk112.xls'.

OPEN DATASET FNAME FOR OUTPUT.

loop at i_finaltab.

count = count + 1.

if count LE 6.

index = row_max * ( count - 1 ) + 1. " 1 - column name

call method of sheet 'Cells' = cells

exporting

#1 = index.

set property of cells 'Value' = i_finaltab-col1.

index = index + 1.

call method of sheet 'Cells' = cells

exporting

#1 = index.

set property of cells 'Value' = i_finaltab-col2.

index = index + 1.

call method of sheet 'Cells' = cells

exporting

#1 = index.

set property of cells 'Value' = i_finaltab-col3.

index = index + 1.

call method of sheet 'Cells' = cells

exporting

#1 = index.

set property of cells 'Value' = i_finaltab-col4.

else.

v_excel_count = v_excel_count + 1.

CALL METHOD OF application 'Sheets' = sheets.

CALL METHOD OF sheets 'Add'.

concatenate 'EXCEL' v_excel_count into sheet_name.

clear count.

count = count + 1.

call method of application 'Worksheets' = sheet

exporting

#1 = 1.

set property of sheet 'Name' = sheet_name.

call method of sheet 'Activate'.

index = row_max * ( count - 1 ) + 1.

perform header_details.

count = count + 1.

index = row_max * ( count - 1 ) + 1. " 1 - column name

call method of sheet 'Cells' = cells

exporting

#1 = index.

set property of cells 'Value' = i_finaltab-col1.

index = index + 1.

call method of sheet 'Cells' = cells

exporting

#1 = index.

set property of cells 'Value' = i_finaltab-col2.

index = index + 1.

call method of sheet 'Cells' = cells

exporting

#1 = index.

set property of cells 'Value' = i_finaltab-col3.

index = index + 1.

call method of sheet 'Cells' = cells

exporting

#1 = index.

set property of cells 'Value' = i_finaltab-col4.

endif.

endloop.

&----

---

*& Form header_details

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

form header_details.

det ?= cl_abap_typedescr=>describe_by_DATA( i_finaltab ).

loop at det->components into wa.

call method of sheet 'Cells' = cells

exporting

#1 = index.

set property of cells 'Value' = wa-name.

index = index + 1.

endloop.

endform. " header_details