excel multiple tab sheets

[Former Member]

how to download data into multiple tab sheets in a single file

[Former Member]

report zole123.

INCLUDE ole2incl.
DATA:  count TYPE i,
       application TYPE ole2_object,
       workbook TYPE ole2_object,
       excel     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, place(50), END OF itab3.


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

  APPEND: 'roshani' TO itab1, 'jain' TO itab2,
                              'worli' TO itab3,
            'nehal' TO itab1, 'shah' TO itab2,
                              'chowpatty' TO itab3,
           'saad' TO itab1, 'sheikh' TO itab2,
                              'versova' TO itab3,
            'sushilnath' TO itab1, 'shukla' TO itab2,
                              'grant road' TO itab3,
            'ajju' TO itab1, 'ratna' TO itab2,
                              'gaon' TO itab3,

            'sanant' TO itab1, 'R.' TO itab2,
                              'mahim' TO itab3.


*  CREATE OBJECT application 'excel.application'.
*  SET PROPERTY OF application 'visible' = 1.
*  CALL METHOD OF application 'Workbooks' = workbook.
*  CALL METHOD OF workbook 'Add'.

  CREATE OBJECT excel 'EXCEL.APPLICATION'.

  IF sy-subrc NE 0.
    WRITE: / 'No EXCEL creation possible'.
    STOP.
  ENDIF.

  SET PROPERTY OF excel 'DisplayAlerts' = 0.

  CALL METHOD OF excel 'WORKBOOKS' = workbook .

  SET PROPERTY OF excel 'VISIBLE' = 1.


*  Create worksheet
  SET PROPERTY OF excel 'SheetsInNewWorkbook' = 1.
  CALL METHOD OF workbook 'ADD'.


  DO 3 TIMES.
    IF sy-index GT 1.
      CALL METHOD OF excel 'WORKSHEETS' = sheet.
      CALL METHOD OF sheet 'ADD'.
      FREE OBJECT sheet.
    ENDIF.
  ENDDO.

  count = 1.
  DO 3 TIMES.

    CALL METHOD OF excel 'WORKSHEETS' = sheet
      EXPORTING
        #1 = count.

*    perform get_sheet_name using scnt sname.
    CASE count.
      WHEN '1'.
        SET PROPERTY OF sheet 'NAME' = 'firstName'.
        CALL METHOD OF sheet 'ACTIVATE'.
        itab1-first_name = 'FIRST NAME'.

        LOOP AT itab1.
          index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name
          CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
          SET PROPERTY OF cells 'Formula' = itab1-first_name.
          SET PROPERTY OF cells 'Value' = itab1-first_name.

        ENDLOOP.

      WHEN '2'.
        SET PROPERTY OF sheet 'NAME' = 'LastName'.
        CALL METHOD OF sheet 'ACTIVATE'.
        LOOP AT itab2.
          index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name
          CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
          SET PROPERTY OF cells 'Formula' = itab2-last_name.
          SET PROPERTY OF cells 'Value' = itab2-last_name.

        ENDLOOP.

      WHEN '3'.
        SET PROPERTY OF sheet 'NAME' = 'place'.
        CALL METHOD OF sheet 'ACTIVATE'.
        LOOP AT itab3.
          index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name
          CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
          SET PROPERTY OF cells 'Formula' = itab3-place.
          SET PROPERTY OF cells 'Value' = itab3-place.

        ENDLOOP.


    ENDCASE.
    count = count + 1.


**

  ENDDO.


* Save excel speadsheet to particular filename

  GET PROPERTY OF excel 'ActiveSheet' = sheet.
  CALL METHOD OF sheet 'SaveAs'
                   EXPORTING #1 = 'c:tempexceldoc1.xls'     "filename
                             #2 = 1.                          "fileFormat