SAP to Excel - OLE Access

Calling MS - Excel from SAP using OLE.

Calling MS - Excel from SAP using OLE.
 

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.
This was first published in October 2001

Dig deeper on SAP Basis administration and NetWeaver administration

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchManufacturingERP

SearchOracle

SearchDataManagement

SearchAWS

SearchBusinessAnalytics

SearchCRM

SearchContentManagement

SearchFinancialApplications

Close