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
Example code and information on various ways of interfacing with Microsoft applications