Tip

SAP to Excel - OLE Access

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 >

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.