Downloading data into multiple work sheets in excel

[Former Member]

Hi All,

Could you please tell me the way of downloading data into multiple work sheets .

Now, we are downloading data into multiple excel files and after that copying all the excel file data in to different work sheets of the single excel manually.

So , here I want directly download the data into different work sheets of single excel file.

Regards,

Siddivinesh Jogu

[Former Member]

Hi Welcome to SCN

see

regards

Sachin

[GauthamV]

You can do it using OLE concept.

SEARCH in SCN by Downloading data into multiple work sheets you will get lot of posts.

[Former Member]

chk this blog:

/people/sap.user72/blog/2006/02/07/downloading-data-into-excel-with-format-options

[Former Member]

HI,

Check this link..

[Former Member]

Hi Siddivinesh,

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

[Former Member]

Hi,

Down load into three 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..

[I355602]

Hi,

Refer:-

Hope this helps you.

Regards,

Tarun

[satyam11]

it s work good logic

[Former Member]

Check this thread.

Regards,

Joan

[Former Member]

Hi,

Refer my post yesterday for the same requirement:

[OLE |https://www.sdn.sap.com/irj/scn/profile?editmode=true&userid=3898821]