topic Re: Create Excel sheet using OLE in Application Development and Automation Discussions

Create Excel sheet using OLE

Former Member — Thu, 05 Mar 2009 09:44:28 GMT

Hi All,

Can any one help me to create an multiple excel sheet in an work book using OLE. I want to create more than 3 sheets in excel, which is default sheets in excel when we one excel.

Re: Create Excel sheet using OLE

Former Member — Thu, 05 Mar 2009 10:24:35 GMT

Hi,

Check the below sample code.

Here I'm downloading the same table twice into 2 different sheets for example purpose.

TYPE-POOLS ole2.
DATA: wf_cell_from  TYPE ole2_object,
      wf_cell_from1 TYPE ole2_object,
      wf_cell_to    TYPE ole2_object,
      wf_cell_to1   TYPE ole2_object,
      wf_excel      TYPE ole2_object,   " Excel object
      wf_mapl       TYPE ole2_object,   " list of workbooks
      wf_map        TYPE ole2_object,   " workbook
      wf_worksheet  TYPE ole2_object,   " Worksheet
      wf_cell       TYPE ole2_object,   " Cell Range
      wf_cell1      TYPE ole2_object,
      wf_range      TYPE ole2_object,   " Range of cells to be formatted
      wf_range2     TYPE ole2_object,
      wf_column1    TYPE ole2_object.   " Column to be Autofit


DATA: BEGIN OF t_hex,
      l_tab TYPE x,
      END OF t_hex.

DATA: wf_deli(1) TYPE c,            "delimiter
      wf_action TYPE i,
      wf_file TYPE string,
      wf_path TYPE string,
      wf_fullpath TYPE string.



TYPES: t_data1(1500) TYPE c,
       int_ty TYPE TABLE OF t_data1. "line type internal table

*All the data was prepared as line type internal tables for faster
*download

DATA: int_matl  TYPE int_ty ,
      int_matl1 TYPE int_ty ,
      wa_matl   TYPE t_data1.

TYPES: BEGIN OF ty_mara,
       matnr TYPE matnr,
       mtart TYPE mtart,
       matkl TYPE matkl,
       meins TYPE meins,
       END OF ty_mara.

DATA: int_mara TYPE STANDARD TABLE OF ty_mara,
      wa_mara TYPE ty_mara.

FIELD-SYMBOLS: <fs> .

DATA: wc_sheets LIKE sy-index.  "no.of sheets
DATA: it_tabemp TYPE filetable,
      gd_subrcemp TYPE i.

CONSTANTS wl_c09(2) TYPE n VALUE 09.

CLEAR wc_sheets.


DEFINE ole_check_error.
  if &1 ne 0.
    message e001(zz) with &1.
    exit.
  endif.
END-OF-DEFINITION.

SELECTION-SCREEN BEGIN OF BLOCK block1 WITH FRAME TITLE text-001.
PARAMETERS: p_file   LIKE rlgrap-filename.
SELECTION-SCREEN END OF BLOCK block1.


AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

  REFRESH: it_tabemp.

  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      window_title         = 'Select File'
*      default_extension    = 'xls'
      default_file_name    = 'Material Details'
*      with_encoding        =
      file_filter          = '*.xls'
      initial_directory    = 'C:\'
      prompt_on_overwrite  = ' '
    CHANGING
      filename             = wf_file
      path                 = wf_path
      fullpath             = wf_fullpath
      user_action          = wf_action
*      file_encoding        =
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4
          .
  IF sy-subrc <> 0.
*   MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*              WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.

  IF wf_action EQ 9.
    MESSAGE 'No File have been Selected' TYPE 'S'.
  ELSE.
    p_file = wf_fullpath.
    PERFORM create_excel.
  ENDIF.

*&---------------------------------------------------------------------*
*&      Form  create_excel
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM create_excel.
  LOOP AT it_tabemp INTO p_file.
  ENDLOOP.
* START THE EXCEL APPLICATION
  CREATE OBJECT wf_excel 'EXCEL.APPLICATION'.
  PERFORM err_hdl.

* PUT EXCEL IN FRONT
  SET PROPERTY OF wf_excel  'VISIBLE' = 1.
  PERFORM err_hdl.

* CREATE AN EXCEL WORKBOOK OBJECT
  CALL METHOD OF wf_excel 'WORKBOOKS' = wf_mapl.
  PERFORM err_hdl.

  SET PROPERTY OF wf_excel 'SheetsInNewWorkbook' = 3. "no of sheets
  PERFORM err_hdl.

  CALL METHOD OF wf_mapl 'ADD' = wf_map.
  PERFORM err_hdl.


*Assign the Delimiter to field  symbol.
  ASSIGN wf_deli TO <fs> TYPE 'X'.
  t_hex-l_tab = wl_c09.
  <fs> = t_hex-l_tab.

  CLEAR int_matl.
  REFRESH int_matl.
  SELECT matnr
       mtart
       matkl
       meins
      FROM mara
      INTO CORRESPONDING FIELDS OF TABLE int_mara.
*first the headings will be displayed  in the excel sheet
  CONCATENATE 'Material Number'
  'Material type'
  'Material Group'
  'Base Unit of Measure'
  INTO wa_matl
  SEPARATED BY wf_deli.
  APPEND wa_matl TO int_matl.


  LOOP AT int_mara INTO wa_mara.
    CONCATENATE wa_mara-matnr
                wa_mara-mtart
                wa_mara-matkl
                wa_mara-meins
                INTO wa_matl
                SEPARATED BY wf_deli.
    APPEND wa_matl TO int_matl.
    CLEAR wa_matl.
  ENDLOOP.

*Copyng thae same contents to another table to display in
*new sheet
  MOVE int_matl TO int_matl1.
  PERFORM f_material_details
  TABLES int_matl
  USING  1.

  PERFORM f_material_details
  TABLES int_matl
  USING  2.


  GET PROPERTY OF wf_excel 'ActiveSheet' = wf_map.
  GET PROPERTY OF wf_excel 'ActiveWorkbook' = wf_mapl.

  CALL FUNCTION 'FLUSH'
    EXCEPTIONS
      cntl_system_error = 1
      cntl_error        = 2
      OTHERS            = 3.
  IF sy-subrc = 0.

    CALL METHOD OF wf_map 'SAVEAS'
      EXPORTING #1 = p_file.
  ENDIF.

  CALL METHOD OF wf_mapl 'CLOSE'.
  CALL METHOD OF wf_excel 'QUIT'.

  FREE OBJECT wf_mapl.
  FREE OBJECT wf_map.
  FREE OBJECT wf_excel.

ENDFORM.                    "create_excel
*&---------------------------------------------------------------------*
*&      Form  ERR_HDL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM err_hdl.

  IF sy-subrc <> 0.
    WRITE: / 'OLE ERROR: RETURN CODE ='(i10), sy-subrc.
    STOP.
  ENDIF.

ENDFORM.                    "ERR_HDL

*-- End of Program
*&---------------------------------------------------------------------*
*&      Form  f_material_details
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM f_material_details
   TABLES lint_matl
  USING l_sheet_no TYPE i.

  DATA: lv_lines TYPE i,
        lv_sheet_name(50) TYPE c.

  wc_sheets = l_sheet_no.
  CASE l_sheet_no.
    WHEN 1.
      lv_sheet_name = 'Material_sheet1'.
    WHEN 2.
      lv_sheet_name = 'Material_sheet2'.
  ENDCASE.


*-- activating the worksheet and giving a  name to it
  CALL METHOD OF wf_excel 'WORKSHEETS' = wf_worksheet
    EXPORTING
    #1 = wc_sheets.
  CALL METHOD OF wf_worksheet 'ACTIVATE'.



  SET PROPERTY OF wf_worksheet 'NAME' = lv_sheet_name.

*--formatting the cells
  CALL METHOD OF wf_excel 'Cells' = wf_cell_from
    EXPORTING
    #1 = 1
    #2 = 1.
  DESCRIBE TABLE lint_matl LINES lv_lines.
  CALL METHOD OF wf_excel 'Cells' = wf_cell_to
    EXPORTING
    #1 = lv_lines
    #2 = 4.
*--range of cells to be formatted (in this case 1 to 4)
  CALL METHOD OF wf_excel 'Range' = wf_cell
    EXPORTING
    #1 = wf_cell_from
    #2 = wf_cell_to.

*--formatting the cells
  CALL METHOD OF wf_excel 'Cells' = wf_cell_from1
    EXPORTING
    #1 = 1
    #2 = 1.
  DESCRIBE TABLE lint_matl LINES lv_lines.
  CALL METHOD OF wf_excel 'Cells' = wf_cell_to1
    EXPORTING
    #1 = lv_lines
    #2 = 1.
  CALL METHOD OF wf_excel 'Range' = wf_cell1  " Cell range for first

                                              " column(Material)
    EXPORTING
    #1 = wf_cell_from1
    #2 = wf_cell_to1.

  SET PROPERTY OF wf_cell1 'NumberFormat' = '@' . "To disply zeros
  "in Material number


  DATA l_rc TYPE i.
*DATA download into excel first sheet
  CALL METHOD cl_gui_frontend_services=>clipboard_export
    IMPORTING
      data         = lint_matl[]
    CHANGING
      rc           = l_rc
    EXCEPTIONS
      cntl_error   = 1
      error_no_gui = 2
      OTHERS       = 4.
  CALL METHOD OF wf_worksheet 'Paste'.

  CALL METHOD OF wf_excel 'Columns' = wf_column1.
  CALL METHOD OF wf_column1 'Autofit'.
  FREE OBJECT wf_column1.

ENDFORM.                    " f_material_details

Regards,

Manoj Kumar P

Edited by: Manoj Kumar on Mar 5, 2009 11:25 AM

Re: Create Excel sheet using OLE

Former Member — Thu, 05 Mar 2009 11:16:40 GMT

hi,

thnx for the solution.

One more query.

Here number of sheets is set as 3.

Can we dynamically set the number of sheets in the workbook depending on the data we have.

Re: Create Excel sheet using OLE

Former Member — Thu, 05 Mar 2009 11:22:47 GMT

Hi,

ya you can.

here for example i called the routine f_material_details as

PERFORM f_material_details

TABLES int_matl

USING 2.

what you can do is you can put this in a loop of table which contains table names and call.

ie.

loop at it_tables into wa_tables.
  PERFORM f_material_details
  TABLES (wa_tables-tabname) "it_tables contains tbale names in the field tabname
  USING  sy-tabix. "sy-tabix give the sheet number
endloop.

Re: Create Excel sheet using OLE

Former Member — Thu, 05 Mar 2009 11:24:10 GMT

Hi,

Tnx for solution.

Here u have given number of sheets in workbook as 3. Can we dynamically create sheet depending on data we have.

Ie., I want to create 5th sheet dynamically.

Re: Create Excel sheet using OLE

Former Member — Thu, 05 Mar 2009 11:31:17 GMT

Hi manoj.

Thnx very much.

Can i contact u personally.

Can u share u r email-id with me.

Re: Create Excel sheet using OLE

Former Member — Fri, 06 Mar 2009 03:31:37 GMT

Hi,

Please check my business card to contact me

Edited by: Manoj Kumar on Mar 6, 2009 4:31 AM

Re: Create Excel sheet using OLE

Former Member — Fri, 06 Mar 2009 04:36:47 GMT

Hi Preveen,

I did this knid of coding for having 2 internal tables data into 2 sheets of one excel file,

Kindly refer this sample code below:



INCLUDE ole2incl.
DATA: application TYPE ole2_object,
       workbook TYPE ole2_object,
       sheet TYPE ole2_object,
       cells TYPE ole2_object.
CONSTANTS: row_max TYPE i VALUE 256.
DATA index TYPE i.

*DATA: BEGIN OF itab1 OCCURS 0, first_name(10), END OF itab1.
*DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.
DATA: BEGIN OF itab3 OCCURS 0, formula(50), END OF itab3.
*


TABLES: vbap,mara.


TYPES: BEGIN OF itab,
       lifnr TYPE lfa1-lifnr,
       land1 TYPE lfa1-land1,
*       name1 TYPE lfa1-name1,
*       ort01 TYPE lfa1-ort01,
       END OF itab.



DATA: BEGIN OF itab2 OCCURS 0,
matnr TYPE mara-matnr,
ersda TYPE mara-ersda,
ernam TYPE mara-ernam,
END OF itab2.
 data : v_row type sy-tabix.

DATA: itab1 TYPE STANDARD TABLE OF itab WITH HEADER LINE.

DATA: IT_XLSTAB TYPE STANDARD TABLE OF ITAB ,
      WA_XLSTAB LIKE LINE OF IT_XLSTAB.

START-OF-SELECTION.

  SELECT lifnr land1 fROM lfa1  INTO CORRESPONDING FIELDS OF TABLE itab1 UP TO 5 ROWS.


  SELECT matnr
  ersda
  ernam
  FROM mara
  INTO CORRESPONDING FIELDS OF
  TABLE itab2 UP TO 5 ROWS.



************************************************************************
*START-OF-SELECTION
START-OF-SELECTION.

  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' = 'Sheet1'.
  clear v_row.
  LOOP AT itab1.
     v_row = sy-tabix.
      perform fill_cell  using  v_row 1  itab1-lifnr.
      perform fill_cell  using  v_row 2 itab1-land1.
  ENDLOOP.

* Create second Excel sheet
  CALL METHOD OF application 'Worksheets' = sheet
                               EXPORTING #1 = 2.
  SET PROPERTY OF sheet 'Name' = 'Sheet2'.
  CALL METHOD OF sheet 'Activate'.
  clear v_row.
  LOOP AT itab2.
     v_row = sy-tabix.

      perform fill_cell using  v_row 1 itab2-matnr.
      perform fill_cell using  v_row 2 itab2-ersda.
      perform fill_cell using  v_row 3 itab2-ernam.

  ENDLOOP.


* Save excel speadsheet to particular filename
  CALL METHOD OF sheet 'SaveAs'
                  EXPORTING #1 = 'c:\temp\excelgeet.xls'     "filename
                            #2 = 1.                          "fileFormat

*  Closes excel window, data is lost if not saved
  SET PROPERTY OF application 'visible' = 0.

*  call method of sheet 'CLOSE'
*
*  EXPORTING #1 = 'YES'.


*&---------------------------------------------------------------------*
*& both the below coding closes the apllication permanently from the task manager also.
*&---------------------------------------------------------------------*

SET PROPERTY OF application 'DisplayAlerts' = 0.
   free OBJECT application.


**  call method of application 'QUIT'.
**
**  FREE OBJECT: APPLICATION,
**               SHEET.


form fill_cell  using row  col val.

    CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = row  #2 = col.
    SET PROPERTY OF cells 'Value' = val.


endform.                    " fill_cell

Hope it helps

Regrds

Mansi

Re: Create Excel sheet using OLE

Former Member — Fri, 06 Mar 2009 05:42:19 GMT

Hi,

This is making three worksheets

Thanks,

Krishna..

Re: Create Excel sheet using OLE

Former Member — Fri, 06 Mar 2009 05:43:24 GMT

Hi,

Making 3 worksheets in one XL


REPORT  ZKC_TEST1.

INCLUDE ole2incl.

DATA: w_cell1 TYPE ole2_object,
      w_cell2 TYPE ole2_object.

*--- Ole data Declarations
DATA: h_excel     TYPE ole2_object, " Excel object
      h_mapl      TYPE ole2_object, " list of workbooks
      h_map       TYPE ole2_object, " workbook
      h_zl        TYPE ole2_object, " cell
      h_f         TYPE ole2_object, " font
      gs_interior TYPE ole2_object, " Pattern

      worksheet   TYPE ole2_object,
      h_cell      TYPE ole2_object,
      h_cell1     TYPE ole2_object,
      range       TYPE ole2_object,
      h_sheet2    TYPE ole2_object,
      h_sheet3    TYPE ole2_object,
      gs_font     TYPE ole2_object,
      e_color     TYPE ole2_object,
      gs_italic   TYPE ole2_object,

      flg_stop(1) TYPE c.
*********************************************************************
** Internal table Declaration
*********************************************************************
DATA: BEGIN OF t_excel OCCURS 0,
      MATNR type mara-matnr, "(18) type c,
      ERSDA type mara-ersda, " (8)  type c,
      ERNAM type mara-ernam, "(12) type c,
      LAEDA type mara-laeda, "(8)  type c,
      AENAM type mara-aenam, "(12) type c,
      VPSTA type mara-vpsta, "(15) type c,
      PSTAT type mara-pstat, "(15) type c,
      END OF t_excel.

DATA: t_excel_bckord LIKE t_excel OCCURS 0 WITH HEADER LINE,
      t_excel_bcklog LIKE t_excel OCCURS 0 WITH HEADER LINE,
      t_excel_blkord LIKE t_excel OCCURS 0 WITH HEADER LINE.
data: wa_excel_bckord like line of t_excel.
TYPES: data1(1500) TYPE c,
       ty          TYPE TABLE OF data1.

DATA: it        TYPE ty WITH HEADER LINE,
      it_2      TYPE ty WITH HEADER LINE,
      it_3      TYPE ty WITH HEADER LINE,
      rec       TYPE sy-tfill,
      deli(1)   TYPE c,
      l_amt(18) TYPE c.

DATA: BEGIN OF hex,
       tab TYPE x,
      END OF hex.

FIELD-SYMBOLS: <fs> .

CONSTANTS cns_09(2) TYPE n VALUE 09.

ASSIGN deli TO <fs> TYPE 'X'.
hex-tab = cns_09.
<fs> = hex-tab.
DATA gv_sheet_name(20) TYPE c .
*---selecting into tables
select MATNR
       ERSDA
       ERNAM
       LAEDA
       AENAM
       VPSTA
       PSTAT
       from mara into table t_excel_bckord
       where pstat = 'KVELBCD'.
wa_excel_bckord-matnr = 'MATNR'.
wa_excel_bckord-ersda = 'ERSDA'.
wa_excel_bckord-ernam = 'ERNAM'.
wa_excel_bckord-laeda = 'LAEDA'.
wa_excel_bckord-aenam = 'AENAM'.
wa_excel_bckord-vpsta = 'VPSTA'.
wa_excel_bckord-pstat = 'PSTAT'.

INSERT wa_excel_bckord
         INTO t_excel_bckord INDEX 1 .


select MATNR
       ERSDA
       ERNAM
       LAEDA
       AENAM
       VPSTA
       PSTAT
       from mara into table t_excel_bcklog
       where pstat = 'KVELBCDP'.
INSERT wa_excel_bckord
         INTO t_excel_bcklog INDEX 1 .

select MATNR
       ERSDA
       ERNAM
       LAEDA
       AENAM
       VPSTA
       PSTAT
       from mara into table t_excel_blkord
       where pstat = 'KEBC'.

INSERT wa_excel_bckord
         INTO t_excel_blkord INDEX 1 .



LOOP AT t_excel_bckord.
  CONCATENATE
  t_excel_bckord-MATNR
  t_excel_bckord-ERSDA
  t_excel_bckord-ERNAM
  t_excel_bckord-laeda
  t_excel_bckord-aenam
  t_excel_bckord-vpsta
  t_excel_bckord-pstat
  INTO it
  SEPARATED BY deli.
  APPEND it.
  CLEAR it.
ENDLOOP.

LOOP AT t_excel_bcklog.
  CONCATENATE
  t_excel_bcklog-matnr
  t_excel_bcklog-ersda
  t_excel_bcklog-ernam
  t_excel_bcklog-laeda
  t_excel_bcklog-aenam
  t_excel_bcklog-vpsta
  t_excel_bcklog-pstat
  INTO it_2
  SEPARATED BY deli.
  APPEND it_2.
  CLEAR it_2.
ENDLOOP.

LOOP AT t_excel_blkord.
  CONCATENATE
  t_excel_blkord-matnr
  t_excel_blkord-ersda
  t_excel_blkord-ernam
  t_excel_blkord-laeda
  t_excel_blkord-aenam
  t_excel_blkord-vpsta
  t_excel_blkord-pstat
  INTO it_3
  SEPARATED BY deli.
  APPEND it_3.
  CLEAR it_3.
ENDLOOP.

*--- start Excel
IF h_excel-header = space OR h_excel-handle = -1.
  CREATE OBJECT h_excel 'EXCEL.APPLICATION'.
ENDIF.
*--- get list of workbooks, initially empty
CALL METHOD OF h_excel 'Workbooks' = h_mapl.
SET PROPERTY OF h_excel 'Visible' = 1.

CALL METHOD OF h_mapl 'Add' = h_map.
gv_sheet_name = 'Back Orders'.
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
SET PROPERTY OF worksheet 'Name' = gv_sheet_name .

*--Formatting the area of additional data 1 and doing the BOLD
CALL METHOD OF h_excel 'Cells' = w_cell1
  EXPORTING
  #1 = 1
  #2 = 1.
CALL METHOD OF h_excel 'Cells' = w_cell2
  EXPORTING
  #1 = 1
  #2 = 50.
CALL METHOD OF h_excel 'Range' = h_cell
  EXPORTING
  #1 = w_cell1
  #2 = w_cell2.

GET PROPERTY OF h_cell  'Font' = gs_font .
SET PROPERTY OF gs_font 'Bold' = 1 .
SET PROPERTY OF gs_font 'Name' = 'Arial' .

GET PROPERTY OF h_cell 'Interior' = e_color.
SET PROPERTY OF e_color 'ColorIndex' = 35.

GET PROPERTY OF h_cell  'Font' = gs_italic .
SET PROPERTY OF  gs_italic 'Italic' = 1 .

DATA l_rc TYPE i.

CALL METHOD cl_gui_frontend_services=>clipboard_export
  IMPORTING
    data                 = it[]
  CHANGING
    rc                   = l_rc
  EXCEPTIONS
    cntl_error           = 1
    error_no_gui         = 2
    not_supported_by_gui = 3
    OTHERS               = 4.

CALL METHOD OF h_excel 'Cells' = w_cell1
  EXPORTING
  #1 = 1
  #2 = 1.

CALL METHOD OF h_excel 'Cells' = w_cell2
  EXPORTING
  #1 = 1
  #2 = 1.

CALL METHOD OF h_excel 'Range' = range
  EXPORTING
  #1 = w_cell1
  #2 = w_cell2.

CALL METHOD OF range 'Select'.

CALL METHOD OF worksheet 'Paste'.

gv_sheet_name = 'Backlog'.
GET PROPERTY OF h_excel 'Sheets' = h_sheet2 .

CALL METHOD OF h_sheet2 'Add' = h_map.

SET PROPERTY OF h_map 'Name' = gv_sheet_name .
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
*--Formatting the area of additional data 1 and doing the BOLD
CALL METHOD OF h_excel 'Cells' = w_cell1
  EXPORTING
  #1 = 1
  #2 = 1.

CALL METHOD OF h_excel 'Cells' = w_cell2
  EXPORTING
  #1 = 1
  #2 = 50.

CALL METHOD OF h_excel 'Range' = h_cell
  EXPORTING
  #1 = w_cell1
  #2 = w_cell2.

GET PROPERTY OF h_cell 'Font' = gs_font .
SET PROPERTY OF gs_font 'Bold' = 1 .

GET PROPERTY OF h_cell 'Interior' = e_color.
SET PROPERTY OF e_color 'ColorIndex' = 40.


CALL METHOD cl_gui_frontend_services=>clipboard_export
  IMPORTING
    data                 = it_2[]
  CHANGING
    rc                   = l_rc
  EXCEPTIONS
    cntl_error           = 1
    error_no_gui         = 2
    not_supported_by_gui = 3
    OTHERS               = 4.

CALL METHOD OF h_excel 'Cells' = w_cell1
  EXPORTING
  #1 = 1
  #2 = 1.

CALL METHOD OF h_excel 'Cells' = w_cell2
  EXPORTING
  #1 = 1
  #2 = 1.

CALL METHOD OF h_excel 'Range' = range
  EXPORTING
  #1 = w_cell1
  #2 = w_cell2.

CALL METHOD OF range 'Select'.

CALL METHOD OF worksheet 'Paste'.
gv_sheet_name = 'Blocked Orders'.
GET PROPERTY OF h_excel 'Sheets' = h_sheet3 .

CALL METHOD OF h_sheet3 'Add' = h_map.
SET PROPERTY OF h_map 'Name' = gv_sheet_name .
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
*--Formatting the area of additional data 1 and doing the BOLD
CALL METHOD OF h_excel 'Cells' = w_cell1
  EXPORTING
  #1 = 1
  #2 = 1.

CALL METHOD OF h_excel 'Cells' = w_cell2
  EXPORTING
  #1 = 1
  #2 = 50.

CALL METHOD OF h_excel 'Range' = h_cell
  EXPORTING
  #1 = w_cell1
  #2 = w_cell2.
GET PROPERTY OF h_cell 'Font' = gs_font .
SET PROPERTY OF gs_font 'Bold' = 1 .

GET PROPERTY OF h_cell 'Interior' = e_color.
SET PROPERTY OF e_color 'ColorIndex' = 45.


CALL METHOD cl_gui_frontend_services=>clipboard_export
  IMPORTING
    data                 = it_3[]
  CHANGING
    rc                   = l_rc
  EXCEPTIONS
    cntl_error           = 1
    error_no_gui         = 2
    not_supported_by_gui = 3
    OTHERS               = 4.

CALL METHOD OF h_excel 'Cells' = w_cell1
  EXPORTING
  #1 = 1
  #2 = 1.

CALL METHOD OF h_excel 'Cells' = w_cell2
  EXPORTING
  #1 = 1
  #2 = 1.

CALL METHOD OF h_excel 'Range' = range
  EXPORTING
  #1 = w_cell1
  #2 = w_cell2.

CALL METHOD OF range 'Select'.

CALL METHOD OF worksheet 'Paste'.
*--- disconnect from Excel
FREE OBJECT h_zl.
FREE OBJECT h_mapl.
FREE OBJECT h_map.
FREE OBJECT h_excel.

Thanks,

Krishna..

Re: Create Excel sheet using OLE

Former Member — Fri, 06 Mar 2009 07:25:51 GMT

Try this I guess this will work ..

Call the method to add workbook

CALL METHOD OF h_excel 'Workbooks' = h_mapl.

CALL METHOD OF h_mapl 'Add' = h_map.

CALL METHOD OF h_excel 'Worksheets' = h_sh

EXPORTING

#1 = 3.