Multiple sheet Excel Document from SAP using OLE

The following ABAP code can be copied and pasted into your ABAP editor directly and when executed will demonstrate how to create a Microsoft Excel document. The example will create a document which has 3 sheets so allows you to see how to create a multiple sheet Excel document.


*&------------------------------------------------------*
*& Report  ZMULTIEXCEL                                  *
*&                                                      *
*&------------------------------------------------------*
*& Author: SAP ABAP Development Website www.SAP Development*
*&                                                      *
*&------------------------------------------------------*
REPORT  MULTIEXCEL                    .
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.
************************************************************************
*START-OF-SELECTION
START-OF-SELECTION.
  APPEND: 'Peter' TO itab1, 'Ivanov' TO itab2,
                              '=Sheet1!A1 & " " & Sheet2!A1' TO itab3,
            'John' TO itab1, 'Smith' TO itab2,
                              '=Sheet1!A2 & " " & Sheet2!A2' 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 first Excel Sheet
  CALL METHOD OF application 'Worksheets' = sheet
                               EXPORTING #1 = 1.
  CALL METHOD OF sheet 'Activate'.
  SET PROPERTY OF sheet 'Name' = 'Sheet1'.
  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 'Value' = itab1-first_name.
  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'.
  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 'Value' = itab2-last_name.
  ENDLOOP.
* Create third Excel sheet
  CALL METHOD OF application 'Worksheets' = sheet
                               EXPORTING #1 = 3.
  SET PROPERTY OF sheet 'Name' = 'Sheet3'.
  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-formula.
    SET PROPERTY OF cells 'Value' = itab3-formula.
  ENDLOOP.
* Save excel speadsheet to particular filename
  CALL METHOD OF sheet 'SaveAs'
                  EXPORTING #1 = 'c:\temp\exceldoc1.xls'     "filename
                            #2 = 1.                          "fileFormat
*  Closes excel window, data is lost if not saved
*  SET PROPERTY OF application 'visible' = 0.


Quick guide to some of the OLE statements for OLE processing in this program as well as a few other ones.

* Save Excel speadsheet to particular filename
CALL METHOD OF sheet 'SaveAs'
                EXPORTING #1 = 'c:\temp\exceldoc1.xls'     "filename
                          #2 = 1.                          "fileFormat
* Save Excel document
CALL METHOD OF sheet 'SAVE'.
* Quits out of Excel document
CALL METHOD OF sheet 'QUIT'.
*  Closes visible Excel window, data is lost if not saved
SET PROPERTY OF application 'visible' = 0.

Related Articles

Multiple sheet Excel Document from SAP using OLE
Example code and information on various ways of interfacing with Microsoft applications