SAP to Excel - OLE Access
Calling MS - Excel from SAP using OLE.
Calling MS - Excel from SAP using OLE.
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
REPORT ZRAM0004 MESSAGE-ID Z1. *----------------------------* * Topic: Example: Calling up Microsoft Excel. * * * * Description: Example program that is calling up EXCEL, transfer * * an internal table and returning to R/3. * * * * Implementing The program uses OLE. * * * * Authoriz. None. * * * * Submitting: Run by SA38, SE38. * * * * Parameters: Excel file path and filename. * * * * Output: Excel file with data. * * * * Customizing: Check that EXCEL is registered in R/3 via transaction * * SOLE. * * * * Change of You only need to do the syntax check at releasechange. * * release: * * * * R/3 Release: 3.1H * * * * Programmer: Ramki S (Courtsey theory : Friend ) * * Date: October 1998. * * * *----------------------------------------------------------------------* PARAMETERS: XLSFILE(64) TYPE C DEFAULT 'C:ptmatrix.xls'. TABLES USR03. DATA: USERS LIKE USR03 OCCURS 100 WITH HEADER LINE ,ITEMS TYPE I. CONSTANTS: OK TYPE I VALUE 0. INCLUDE OLE2INCL. DATA: EXCEL TYPE OLE2_OBJECT, WORKBOOK TYPE OLE2_OBJECT, SHEET TYPE OLE2_OBJECT, CELL TYPE OLE2_OBJECT, ROW TYPE OLE2_OBJECT. *----------------------------------------------------------------------* * You find SAP OLE programs under development Class 'SOLE' * * * * MSTAPPL Table Maintenance APPL * * RSOLEDOC Document list * * RSOLEIN0 OLE Load Type Information * * RSOLEINT Type Info Loaded * * RSOLETI0 OLE Object Browser * * RSOLETI1 OLE Object Browser * * RSOLETI2 OLE Object Browser * * RSOLETI3 F4 Help For OLE Objects * * RSOLETT1 OLE 2.0 Automation Demo Program * * * * Transactions: * * SOLE * * SOLO - List of OLE applcations with loaded type info * * * * * * You will find the decription of possible objects and methods in the * * windows help file for excel. * *----------------------------------------------------------------------* * Create an Excel object and start Excel. CREATE OBJECT EXCEL 'EXCEL.APPLICATION'. IF SY-SUBRC NE OK. MESSAGE I000 WITH SY-MSGLI. ENDIF. * Create an Excel workbook Object. CALL METHOD OF EXCEL 'WORKBOOKS' = WORKBOOK . * Put Excel in background SET PROPERTY OF EXCEL 'VISIBLE' = 0 . * Collect the data to the transfer. SELECT * FROM USR03 INTO TABLE USERS. DESCRIBE TABLE USERS LINES ITEMS. CHECK ITEMS > 0. * Put Excel in front. SET PROPERTY OF EXCEL 'VISIBLE' = 1 . * Transfer the header line to Excel. CALL METHOD OF WORKBOOK * 'NEW' EXPORTING #1 = XLSFILE. 'OPEN' EXPORTING #1 = XLSFILE. CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'A1'. SET PROPERTY OF CELL 'VALUE' = 'BNAME' . CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'B1'. SET PROPERTY OF CELL 'VALUE' = 'NAME1' . * Transfer the internal table values to Excel LOOP AT USERS. CALL METHOD OF EXCEL 'ROWS' = ROW EXPORTING #1 = '2' . CALL METHOD OF ROW 'INSERT' NO FLUSH. CALL METHOD OF EXCEL 'RANGE' = CELL NO FLUSH EXPORTING #1 = 'A2' . SET PROPERTY OF CELL 'VALUE' = USERS-BNAME NO FLUSH. CALL METHOD OF EXCEL 'RANGE' = CELL NO FLUSH EXPORTING #1 = 'B2' . SET PROPERTY OF CELL 'VALUE' = USERS-NAME1 NO FLUSH. ENDLOOP. * release and exit Excel. CALL METHOD OF EXCEL 'SAVE'. CALL METHOD OF EXCEL 'QUIT'. * Free all objects FREE OBJECT CELL. FREE OBJECT WORKBOOK. FREE OBJECT EXCEL. EXCEL-HANDLE = -1. FREE OBJECT ROW.