Downloading to Excel with OLE concept

[Former Member]

Hi All,

I have created a program in 6.0 version which gives my exact requirement.

Please copy the code and execute the program.

&----

---

*& Report ZSK123 *

*& *

&----

---

*& *

*& *

&----

---

REPORT ZSK123 .

type-pools: ole2.

DATA: H TYPE I.

data: g_sno(5) type n.

DATA: H_ZL TYPE OLE2_OBJECT,

H_F TYPE OLE2_OBJECT.

types: begin of x_comment,

comment type string,

end of x_comment.

DATA: H_EXCEL TYPE OLE2_OBJECT, " EXCEL OBJECT

H_MAPL TYPE OLE2_OBJECT, " LIST OF WORKBOOKS

H_MAP TYPE OLE2_OBJECT. " WORKBOOK

types: begin of x_jobs,

sno(3) type n,

jobname(50) type c,

comment type string,"zrtr,

end of x_jobs.

DATA: W_STRING(2000) TYPE C.

data: t_jobs type standard table of x_jobs,

w_jobs type x_jobs.

data: hexcel type ole2_object,

hrange type ole2_object.

data: l_string type string,

w_tabix type sy-tabix.

w_jobs-sno = 100.

w_jobs-jobname = 'HAA'.

APPEND W_JOBS TO T_JOBS.

w_jobs-sno = 2.

w_jobs-jobname = 'BAI'.

APPEND W_JOBS TO T_JOBS.

loop at t_jobs into w_jobs.

w_tabix = sy-tabix.

w_jobs-comment = 'What is your name'.

concatenate w_jobs-comment 'My name is Subhani' into w_jobs-comment

separated by cl_abap_CHAR_UTILITIES=>newline.

modify t_jobs from w_jobs index w_tabix transporting comment.

endloop.

CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.

SET PROPERTY OF H_EXCEL 'VISIBLE' = 1.

• GET LIST OF WORKBOOKS, INITIALLY EMPTY

CALL METHOD OF H_EXCEL 'WORKBOOKS' = H_MAPL.

• PERFORM ERR_HDL.

• ADD A NEW WORKBOOK

CALL METHOD OF H_MAPL 'ADD' = H_MAP.

• PERFORM ERR_HDL.

• OUTPUT COLUMN HEADINGS TO ACTIVE EXCEL SHEET

PERFORM FILL_CELL USING 1 1 1 000 'SNO'(001).

PERFORM FILL_CELL USING 1 2 1 000 'JOBNAME'(002).

PERFORM FILL_CELL USING 1 3 1 000 'COMMENT'(003).

LOOP AT T_JOBS INTO W_JOBS.

• COPY DATATO ACTIVE EXCEL SHEET

H = SY-TABIX + 1.

PERFORM FILL_CELL USING H 1 0 000 W_JOBS-SNO.

PERFORM FILL_CELL USING H 2 0 000 W_JOBS-JOBNAME.

PERFORM FILL_CELL USING H 3 0 000 W_JOBS-COMMENT.

ENDLOOP.

• CALL METHOD OF H_EXCEL 'WORKBOOKS' = H_MAPL.

CALL METHOD OF H_EXCEL 'WORKSHEETS' = H_MAPL." EXPORTIN G #1 = 2.

• PERFORM ERR_HDL.

• ADD A NEW WORKBOOK

CALL METHOD OF H_MAPL 'ADD' = H_MAP EXPORTING #1 = 2.

• PERFORM ERR_HDL.

• TELL USER WHAT IS GOING ON

SET PROPERTY OF H_MAP 'NAME' = 'COPY'.

LOOP AT T_JOBS INTO W_JOBS.

• COPY FLIGHTS TO ACTIVE EXCEL SHEET

H = SY-TABIX + 1.

PERFORM FILL_CELL USING H 1 0 000 W_JOBS-SNO.

PERFORM FILL_CELL USING H 2 0 000 W_JOBS-JOBNAME.

PERFORM FILL_CELL USING H 3 0 000 W_JOBS-COMMENT.

ENDLOOP.

FREE OBJECT H_EXCEL.

• PERFORM ERR_HDL.

&----

---

*& FORM ERR_HDL

&----

---

• OUTPUTS OLE ERROR IF ANY *

----

---

• --> P1 TEXT

• <-- P2 TEXT

----

---

FORM ERR_HDL.

IF SY-SUBRC <> 0.

WRITE: / 'BATCH JOB AUTOMATION CARRIED OUT SUCCESFULLY'.

STOP.

ENDIF.

ENDFORM. " ERR_HDL .

----

---

• FORM FILL_CELL *

----

---

• SETS CELL AT COORDINATES I,J TO VALUE VAL BOLDTYPE BOLD *

----

---

FORM FILL_CELL USING I J BOLD COL VAL.

CALL METHOD OF H_EXCEL 'CELLS' = H_ZL EXPORTING #1 = I #2 = J.

• PERFORM ERR_HDL.

SET PROPERTY OF H_ZL 'VALUE' = VAL .

• PERFORM ERR_HDL.

GET PROPERTY OF H_ZL 'FONT' = H_F.

• PERFORM ERR_HDL.

SET PROPERTY OF H_F 'BOLD' = BOLD .

• PERFORM ERR_HDL.

SET PROPERTY OF H_F 'COLOR' = COL.

• PERFORM ERR_HDL.

ENDFORM. "FILL_CELL

But in 4.6c there is no method called cl_abap_char_utilities=>newline. So how can i get the same output in 4.6c. please help.

Regards,

Subhani.

[gopi_narendra]

Hope this FM MS_EXCEL_OLE_STANDARD_DAT helps you.

Regards

Gopi

[former_member223537]

<b>data : l_newline(4) TYPE x VALUE '0D0A'.</b>

loop at t_jobs into w_jobs.

w_tabix = sy-tabix.

w_jobs-comment = 'What is your name'.

concatenate w_jobs-comment 'My name is Subhani' into w_jobs-comment

separated by <b>l_newline</b>.

modify t_jobs from w_jobs index w_tabix transporting comment.

endloop.

Message was edited by:

Prashant Patil

[Former Member]

I don't see any method in class cl_abap_char_utilities with name newline.

But when i search class for this string, i see

constants NEWLINE type ABAP_CHAR1 value %_NEWLINE. "#EC NOTEXT

Check this once in your system too.

And you can declare similar variable in 46C and use the same in the program.