topic OLE programming - read data from EXCEL file in Application Development and Automation Discussions

OLE programming - read data from EXCEL file

Former Member — Fri, 20 Jun 2008 13:17:15 GMT

Hello all,

I have some requirement like this...

Open an Excel document, read data from one of the cells and close the document. There are two more things I need to take care of with this.

1. After I read the data from a document, I need to take that document and put it somewhere else.

2. There could be any number of Excel documents in the folder on my PC. I would not have the file names in my hand. I've to read all the documents that are in the folder.

Any thoughts with sample code is appreciated.

Re: OLE programming - read data from EXCEL file

Former Member — Fri, 20 Jun 2008 13:40:27 GMT

Hello,

Please see these: [https://www.sdn.sap.com/irj/sdn/wiki?path=/display/abap/how%2bto%2bread%2bexcel%2bfile%2bfrom%2bapplication%2bor%2bpresentation%2bserver%2band%2bdownload%2binto%2binternal%2btable.]

[/people/subramanian.venkateswaran2/blog/2006/10/02/enhanced-file-upload--uploading-and-processing-excel-sheets]

[/people/kathirvel.balakrishnan2/blog/2006/05/08/data-upload-into-sap-from-microsoft-excel-150-abap-part]

Regards.

Re: OLE programming - read data from EXCEL file

Former Member — Fri, 20 Jun 2008 13:47:22 GMT

hiii

you can open & display data by using following code in excel file



DATA:
 excel       TYPE ole2_object,
 workbook    TYPE ole2_object,
 book        TYPE ole2_object,
 activesheet TYPE ole2_object,
 range       TYPE ole2_object,
 cell        TYPE ole2_object,
 bold        TYPE ole2_object,
 w_temp     TYPE ole2_object,         " OLE Object for Sheet
 w_sheet    TYPE ole2_object,         " OLE Object for Sheet
 interior    TYPE ole2_object.
******Here Fetch data from table********

LOOP AT t_material.
    MOVE 1 TO  w_col_num.
    ADD  1 TO  w_row_num.
* For selecting the cell
    CALL METHOD OF excel 'CELLS' = cell NO FLUSH
      EXPORTING
        #1 = w_row_num
        #2 = w_col_num.
    SET PROPERTY OF cell 'NumberFormat' = '@'.
    SET PROPERTY OF cell 'VALUE'               = t_material-matnr.
    SET PROPERTY OF cell 'HorizontalAlignment' =  -4108.
    SET PROPERTY OF cell 'ColumnWidth'         = 12.
.


* For displaying the matnr information in the cell.
    ADD 1 TO w_col_num.
    MOVE w_row_num TO w_char.
    CONCATENATE 'B' w_char  INTO w_cell1.
    CONCATENATE 'D' w_char  INTO w_cell2.

* Merging third(b1), fouth(c1) cells of row one.
    CALL METHOD OF excel 'range' = range NO FLUSH
      EXPORTING
        #1 = w_cell1
        #2 = w_cell2.
    CALL METHOD OF range 'merge'.

* For bordering the material description
    CALL METHOD OF RANGE 'BORDERAROUND'
      EXPORTING
       #1 = 1
       #2 = 2
       #3 = -4105
       #4 = 35000.

* For putting the data inthe corresponding cell.
    CALL METHOD OF excel 'CELLS' = cell NO FLUSH
      EXPORTING
        #1 = w_row_num
        #2 = w_col_num.
    SET PROPERTY OF cell 'VALUE'               = t_material-maktx.
    SET PROPERTY OF cell 'HorizontalAlignment' =  -4108.
    SET PROPERTY OF cell 'ColumnWidth'         = 12.

  ENDLOOP.

reward if useful

thx

twinkal