Posted: Sat Nov 17, 2007 7:48 pm Post subject: Interface between R/3 and EXCEL using OLE2
Code:
REPORT ZZBGS700 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. *
* In R/3 release 3.0x, the command set of ABAP/4 ontepreter has been *
* increased with keywords that allow access to external objects through*
* OLE2. In SAP the new keywords for OLE2 are: *
* *
* 1. Create Object *
* 2. Set Property *
* 3. Get Property *
* 4. Call Method *
* 5. Free Object *
* *
* *
* Authoriz. None. *
* *
* Submitting: Run by SA38, SE38. *
* *
* Parametre: 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: Developed and tested in R/3 Release: *
* 3.0C *
* *
* Programmer: Benny G. Soerensen *
* Date: July 1997. *
* *
*------------------------------ Questions -----------------------------*
* Version 1
*-------------------------------Corrections----------------------------*
* Date Userid Correction Text *
* ::.::.:::: :::::::: :::::::::::::: ::::::::::::::::::::::::::::: *
*----------------------------------------------------------------------*
PARAMETERS: XLSFILE(64) TYPE C DEFAULT
'C:\BGS\SAP\MODUL-BC\OLE\ZZBGS700.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 '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'.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
