Skip to Content

I recently got involved with a project where the user wanted to update an existing Excel spreadsheet automatically via an ABAP program.  I started digging in and found that this could be accomplished using OLE technology.   This weblog gives an small example application which any beginner could use to get their application started.

ABAP Source Code

report zole_example. 

include ole2incl.

data: e_sheet type ole2_object.

data: e_appl  type ole2_object.

data: e_work  type ole2_object.

data: e_cell  type ole2_object. 

data: field_value(30) type c. 

parameters: p_file type localfile default ‘C:RichTest.xls’.

start-of-selection. 

* Start the application  

create object e_appl ‘EXCEL.APPLICATION’.  

set property of e_appl ‘VISIBLE’ = 1.

* Open the file 

call method of e_appl ‘WORKBOOKS’ = e_work.   

call method of e_work ‘OPEN’  

        exporting   #1 = p_file.

* Write data to the excel file

  do 20 times.

* Create the value  

  field_value  = sy-index.    

  shift field_value left deleting leading space.    

  concatenate ‘Cell’ field_value  into field_value separated by space. 

* Position to specific cell  in  Column 1    

  call method of e_appl ‘Cells’ = e_cell  exporting  #1 = sy-index  #2 = 1.

* Set the value  

  set property of e_cell ‘Value’ = field_value . 

* Position to specific cell  in  Column 2   

  call method of e_appl ‘Cells’ = e_cell  exporting  #1 = sy-index  #2 = 2.

* Set the value   

  set property of e_cell ‘Value’ = field_value .

  enddo. 

* Close the file  

  call method of e_work ‘close’.

* Quit the file 

  call method of  e_appl  ‘QUIT’. 

  free object e_appl.

Result

image

 

For more information on ABAP/OLE,  search on “OLE” in the ABAP forum and check out this great article called An Easy Reference For OLE Automation by Serdar Simsekler.  This guide will go over more advanced topics and has a lot more code samples.

To report this post you need to login first.

20 Comments

You must be Logged on to comment or reply to a post.

  1. Former Member
    Nice tutorial for start easy with OLE/ABAP.

    By the way, does anybody have an idea for protecting a cell in excel. I’ve been trying many methods by suggestion with object programming in VB but with no success.

    Help would be great.
    Thanks a lot

    (0) 
  2. Former Member
    hello G’day,
    Nice and excellent, as if we have a function module to download it in excel sheet, but if we go performance level your coding makes good.
    regards
    (0) 
  3. Paul Dean
    Rich

    Great article, but just a quick question

    Do you have to explicitly save the spreadsheet or should the ‘close’ statement take care of this.

    I ask only as I have tried to implement the code & I get a popup asking to save the spreadsheet

    If I add ‘call method of workbook ‘SAVE’.’ to my logic, the abap finishes with the spreadsheet still open & not saved. Could this be something to do with the windows security employed at my client site.

    Cheers

    Paul.

    (0) 
  4. Hi Rich,

    I try to run your coding and the coding didnt work.

    I run it and the spreadsheet display out a while without data and close immediately. Why this can be happened?

    The microsoft office I am using is 2003.

    (0) 
  5. Hi,

    I know why it is not working.

    I need to create the excel file at the very first before I can successfully run the program.

    It there possible that the program can automatically generate the excel file at the same time?

    (0) 
  6. Former Member
    hi Rich,
    whenever I run the this prog I need to explicitly save the file as I get pop up asking for saving file. Even call method of ‘save’ is not working.
    Can you please help me out.
    (0) 
  7. Hi Rich,

    Great blog! It was certainly useful as I’m a beginner to all these OLE stuff.

    However, could you please provide some help for the following? I now have a requirement to generate random numbers in a certain range of cells within the Excel spreadsheet.

    I have looked through the different methods and believe that I should be using the Randomize and Rnd methods, but I’m not exactly sure as to how it should be coded. Could you please help me out?

    Also, the link that you gave for the article “An Easy Reference for OLE Automation” doesn’t seem to exist anymore. Do you have the new link?

    Thanks for your help!

    Regards,
    Adeline.

    (0) 
  8. Former Member
    Hi everyone,

    I’ve a problem about the abap report program.
    when I use abap to create an excel(sheet1) object, and fill the value, then need to copy (sheet1) to (sheet2) , how to coding it in abap program??

    my source code as below:
    CREATE OBJECT h_excel ‘Excel.Application’.
    SET PROPERTY OF h_excel ‘Visible’ = 1.
    GET PROPERTY OF h_excel ‘Workbooks’ = h_workbooks.
    CALL METHOD OF h_workbooks ‘Add’
    EXPORTING #1 = p_upload. –> sheet(1)

    ***** I’d like to copy sheet(1) format to sheet(2) here ******

    CALL METHOD OF h_excel ‘Worksheets’ = h_sheet.
    CALL METHOD OF h_sheet ‘Add’.
    GET PROPERTY OF h_excel ‘ActiveSheet’ = h_sheet.
    SET PROPERTY OF h_sheet ‘NAME’ = ‘sheet(2)’. –> sheet(2)
    CALL METHOD OF h_excel ‘Worksheets’ = h_sheet
    EXPORTING #1 = ‘sheet(2)’.

    Please help me out!!
    Thanks & Best Regards,
    Eunice

    (0) 
  9. Former Member
    This was really great weblog, it solved many of my problems, but i am facing one problem that i am not able to use itab in “*create data block”,when i use itab it dosent give me output, can any one pls tell me where i am wrong i am attaching my code with this.
    include ole2incl.

    data: e_sheet type ole2_object.
    data: e_appl  type ole2_object.
    data: e_work  type ole2_object.
    data: e_cell  type ole2_object.

    data: field_value(30) type c.

    parameters: p_file type localfile default ‘C:\test.xls’.
    DATA: begin of itab OCCURS 0 ,
           field1(10) type c,
           field2(10) type c.
    DATA end of itab.

    itab-field1 = ‘ROHIT1’.
    itab-field2 = ‘ROHIT2’.
    append itab.

    itab-field1 = ‘ROHIT3’.
    itab-field2 = ‘ROHIT4’.
    append itab.

    itab-field1 = ‘ROHIT5’.
    itab-field2 = ‘ROHIT6’.
    append itab.

    itab-field1 = ‘ROHIT7’.
    itab-field2 = ‘ROHIT8’.
    append itab.

    DATA wa like itab.
    DATA : field1 like itab-field1,
          field2 like itab-field2.

    start-of-selection.

    * Start the application
      create object e_appl ‘EXCEL.APPLICATION’.
      set property of e_appl ‘VISIBLE’ = 1.

    * Open the file
      call method of e_appl ‘WORKBOOKS’ = e_work.
      call method of e_work ‘OPEN’
        EXPORTING
          #1 = p_file.

      get property of e_appl ‘ActiveSheet’ = e_sheet.

    * open the worksheet
      call method of e_appl ‘WORKSHEETS’ = e_sheet
        EXPORTING
          #1 = 1.
      call methoD of e_sheet ‘ACTIVATE’.

    *  set worksheet name
      set property of e_sheet ‘NAME’ = ‘rowdata’.

    * Write data to the excel file

    loop at itab into wa.

    * Create the value
    field1 = wa-field1.
    field2 = wa-field2.

    * Position to specific cell  in  Column 1
        call method of e_appl ‘Cells’ = e_cell
          EXPORTING
            #1 = sy-index
            #2 = 2.

    * Set the value

        set property of e_cell ‘Value’ = field1.

    * Position to specific cell  in  Column 2
        call method of e_appl ‘Cells’ = e_cell
          EXPORTING
            #1 = sy-index
            #2 = 3.
    * Set the value

        set property of e_cell ‘Value’ = field2.

    endloop.

    * Close the file
      call method of e_work ‘close’.

    * Quit the file
      call method of e_appl ‘QUIT’.

      free object e_appl.

    (0) 
  10. Former Member
    Interesting blog. I have to open not an explicit file, but the Excel Workbook that is currently opened on the user desktop. How can I obtain this?

    Thanks

    Massimo

    (0) 
  11. Former Member
    The performance of OLE2 downloads is rather slow when updating one cell at a time and will certainly show when you have a few thousand cells being filled.
    To improve the round trip performance, queue the commands using ‘no flush’, i.e.

      call method of go_excel
        ‘CELLS’ = go_cell
        no flush
        exporting
          #1 = p_row
          #2 = p_column.
      set property of go_cell  ‘VALUE’ = l_val no flush.

    Makes a hell of a difference.

    Even faster is the clipboard method seen in other posts, but beware, for some reason this method won’t always transfer some characters used in non-English languages.

    Regarding random via OLE2, here is how to do it.

    *   Select a cell.
        call method of h_excel ‘Cells’ = h_cell exporting #1 = 1 #2 = 1.

    *   N.B. You have to SET each time otherwise the same number comes back.
        set property of h_cell ‘Value’ = ‘=RAND()’.
    *   Pull the random value.
        get property of h_cell ‘Value’ = rand .

    (0) 
  12. Former Member
    I have extensively worked on SAP R/3 and Excel integration and the best thing I liked is about using this SAP-Excel link is for Mathmatical calculation. In my scenario, I have to create a 30X30 matrix and retrieve variable values by inversing matrix and multiplying it with results. it is so quick to develop in Excel as it provides MMULT and MINVERSE functions and it is quite faster execution takes plce in ABAP. Just imagin if I have to code it in ABAP and run it on R/3 what would be performance issue? So I suggest if we are talking about performances, it depends upto our scenarios what we want to chose.
    (0) 
    1. Former Member

      Hi Varun,

      I’ve also encountered the same problem. What I did is, I created the excel sheet first in the path that I have mentioned.

      This way, when we run the program by rich, it will call this excel and start populating it.

      I was amazed that the opened excel is populating without me typing anything. ๐Ÿ™‚

      Kr,

      Maki

      (0) 
  13. Former Member

    Hello Rich,

    can we insert macros into excel from a report in SAP…?

    i have a requirement where i need to auto calculate numeric data in rows.

    thanks,
    Phanindra

    (0) 

Leave a Reply