Getting excel file data from different server.

[Former Member]

HI all,

I have done one interface program.In that i am getting one text file data by using FTP connections successfully.

FTP_CONNECT

FTP_COMMAND

FTP_CLIENT_TO_R3

FTP_R3_TO_CLIENT

Now my requirement has been changed to get excel file data instead of text file data from the non sap server.

I have tried in same way as getting text file data.but it is going to dump and some times getting some #$## like this in my internal table.

So pls guide me what is the procedure to get excel file data from other server by using FTP connections only.

Regards,

Lokesh

[Former Member]

Hello Lokseh,

FTP is about copying file, opening excel file is another process.

I suggest you to put it into 2 separate process:

First, copy file from remote server into local server.

Second, open excel file using ABAP OLE automation.

Hope it helps,

regards,

Indu.

[Former Member]

hi,

Saving file on application server as CSV and fetchin that file from application server is thr FTP only..

so if you are facing problem using statements you hav used then try the way as i told u..

[Former Member]

Hello Lokesh,

Maybe, ABAP did not recognized Excel format, so to make an excel file readable, ABAP must use OLE as an interpreter.

Here is the definition in wikipedia for OLE Automation:

http://en.wikipedia.org/wiki/OLE_Automation

And here is the sample of accessing excel from ABAP:

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'.

*  Free all objects
   FREE OBJECT CELL.
   FREE OBJECT WORKBOOK.
   FREE OBJECT EXCEL.
   EXCEL-HANDLE = -1.
   FREE OBJECT ROW.

Hope it might prove helpful.

Regards

Indu.