Problem solve
Get help with specific problems with your technologies, process and projects.
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.
Step 2 of 2:
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.
