Skip to Content
0
Former Member
Oct 03, 2007 at 03:56 AM

Downloading to Excel with OLE concept

218 Views

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.