Integrating MS Word and MS-Excel with ABAP-OLE

Former Member · ‎04-03-2013

OLE (Object linking and embedding) in SAP is used to integrate MS-excel and MS-word eand to achieve automation through programming interface . When called from an ABAP program, the SAPGUI acts as OLE client, and the desktop application as the OLE server.

In general there are five basic statements to achieve automation:

CREATE OBJECT
SET PROPERTY
GET PROPERTY
CALL METHOD
FREE OBJECT

1. Creating an OLE Object

To create an OLE object, the ABAP statement “CREATE OBJECT” is used.

The syntax is CREATE OBJECT obj class.

Here, “obj” is the handle variable for the base object and “class” is the specific identifier for the corresponding application.

e.g. CREATE OBJECT gs_word 'WORD.APPLICATION' .

If the creation is successful the value of “sy-subrc” becomes “0”, otherwise it becomes some other value (i.e. “1”, “2” or “3” with respect to the error type).

2. Calling a Method of an Object

After creating an OLE object, it is possible to call its methods to execute its functionality. This is achieved by using the ABAP statement “CALL METHOD OF”. Also the required parameters can be passed using this statement.

The syntax is:CALL METHOD OF obj m [= f] [EXPORTING p1 = f1 ... pn = fn] .

Here, “obj” is the object handle variable, “m” is the method name, “f” is the variable where the output of the method will be replaced and “pn = fn” assignments are used to pass parameters. The “EXPORTING…” part must be at the end of the statement. For the moment, parameter passing is done by giving their positions and the corresponding value.

e.g. CALL METHOD OF gs_word 'Documents' = gs_documents .

CALL METHOD OF gs_selection 'TypeText' EXPORTING #1 = ip_text .

Successful method calls makes “sy-subrc” value “0”, and unsuccessful cases make it some other value.

3. Setting a Property of an Object

To set a property of an OLE object, the ABAP statement “SET PROPERTY OF” is used.

The syntax is: SET PROPERTY OF obj p = f .

Here, “obj” is the object handle variable, “p” is the property name and “f” is the value to be assigned.

e.g. SET PROPERTY OF gs_word 'Visible' = 1 .

Operation result status is indicated at the system variable “sy-subrc”; “0” for successful operations and another value for erroneous cases.

4. Getting a Property of an Object

Getting the value of a property of an OLE object is obviously similar to setting it. For this, the ABAP statement “GET PROPERTY OF” is used.

The syntax is: GET PROPERTY OF obj p = f .

Here, “obj” is the object handle variable, “p” is the property name and “f” is the variable to which the value of the property is assigned.

e.g. GET PROPERTY OF gs_view 'Type' = gv_viewtype .

Again, operation result status is indicated at the system variable “sy-subrc”; “0” for successful operations and another value for erroneous cases.

5. Freeing an Object

Generally for performance issues, it is required to free the memory allocated for OLE objects. For this, the ABAP statement “FREE OBJECT” is used.

The syntax is: FREE OBJECT obj. where “obj” is the object handle variable.

6. NO FLUSH Addition

Normally, OLE statements are buffered by the ABAP processor and executed at the frontend collectively before the first statement which is not of OLE context. Using this addition prevents this and postpones the execution till just before the first non-OLE statement coming after an OLE statement without NO FLUSH addition.

Please find below , the piece of code for downloading the data from an internal table to a excel sheet:

*&---------------------------------------------------------------------*
*&      Form DOWNLOAD_SHEET
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_L_TABIX text
*      -->P_L_NAME text
*      -->P_P_X_JOB_STATUS_RELEASED text
*      <--P_LW_EXCEL text
*      <--P_LW_WORKSHEET text
*----------------------------------------------------------------------*
FORM download_sheet USING    p_lw_sheet TYPE ole2_object
                              p_l_tabix TYPE i
                              p_l_name   TYPE string
                              p_data TYPE tt_data
*                              p_p_x_job_status_released type tt_tbtco
                     CHANGING p_lw_excel     TYPE ole2_object
                              p_lw_worksheet TYPE ole2_object.
DATA: lw_columns TYPE ole2_object,
        lw_rows TYPE ole2_object,
        lw_column_ent TYPE ole2_object,
        lw_cell TYPE ole2_object,
        lw_int TYPE ole2_object,
        lw_bor TYPE ole2_object,
        lw_tab TYPE ole2_object,
        lw_range TYPE ole2_object,
        lw_font TYPE ole2_object,
        lw_select TYPE ole2_object,
        lw_delete TYPE ole2_object.
*        lw_int TYPE ole2_object.

DATA : "lx_display TYPE ty_product_sheet,
         "lx_data TYPE ty_data ,
         i_row TYPE i,
         i_col TYPE i.
"lx_header TYPE ty_header.

DATA: l_cnt TYPE i VALUE 17 ,
        l_rows TYPE i,
        l_row TYPE i VALUE 1,
        w_rc TYPE i,
        l_rec(1000) TYPE c,
        li_itab TYPE STANDARD TABLE OF t_data,
        l_del TYPE c,
        l_line TYPE i,
        l_hex TYPE x,
        l_pos(3) TYPE c,
        l_char(3) TYPE c,
        l_pos1(5) TYPE c,
        l_len TYPE i,
        l_col1 TYPE i,
        l_len1 TYPE i,
        l_pos2(5) TYPE c,
        li_p_data1 TYPE tt_data.

l_col1 = 1.
l_del = w_del .
w_index = w_index + 1 .

GET PROPERTY OF p_lw_excel 'Sheets' = p_lw_worksheet .
CALL METHOD OF p_lw_worksheet 'Add' = p_lw_sheet.
SET PROPERTY OF p_lw_sheet 'Name' = p_l_name .
GET PROPERTY OF p_lw_excel 'ACTIVESHEET' = p_lw_worksheet.

CALL METHOD OF p_lw_worksheet 'TAB' = lw_tab.
SET PROPERTY OF lw_tab 'ColorIndex' = w_index.

CALL METHOD OF p_lw_excel 'Columns' = lw_columns.
SET PROPERTY OF lw_columns 'Locked' = 0.

DESCRIBE TABLE p_data LINES l_rows .

CALL METHOD cl_gui_frontend_services=>clipboard_export
    IMPORTING
      data                 = p_data[]
    CHANGING
      rc                   = w_rc
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.

* Paste the contents in the clipboard to the worksheet
CALL METHOD OF p_lw_worksheet 'Paste'.

DESCRIBE TABLE p_data LINES l_len .

APPEND LINES OF p_data FROM 7 TO l_len TO li_p_data1 .
IF li_p_data1[] IS NOT INITIAL.
READ TABLE li_p_data1[] INTO l_rec INDEX 1.
SPLIT l_rec AT l_del INTO TABLE li_itab.
ENDIF.
DESCRIBE TABLE li_itab LINES l_line.
DO 6 TIMES .

CALL METHOD OF p_lw_excel 'Cells' = lw_cell EXPORTING #1 = sy-index #2 = 1 .
GET PROPERTY OF lw_cell 'Font' = lw_font.

    IF sy-index = 1 OR sy-index = 2 .
      SET PROPERTY OF lw_font 'Bold' = 1 .
      SET PROPERTY OF lw_font 'Size' = 14 .

    ELSE.
      SET PROPERTY OF lw_font 'Bold' = 1 .
      SET PROPERTY OF lw_font 'Size' = 10 .
    ENDIF.

ENDDO.
CLEAR: lw_font,lw_cell .
DO l_line TIMES .
CALL METHOD OF p_lw_excel 'Cells' = lw_cell EXPORTING #1 = 7 #2 = sy-index.
GET PROPERTY OF lw_cell 'Font' = lw_font.

SET PROPERTY OF lw_font 'Bold' = 1 .
ENDDO.

l_char = l_line.
PERFORM create_address USING l_line CHANGING l_pos.
l_pos1 = l_pos.
l_len1 = STRLEN( l_pos1 ).
IF l_len1 = 3.
    l_pos+2(1) = 7.
ELSE.
    l_pos+1(1) = 7.
ENDIF.
CALL METHOD OF p_lw_excel 'Range' = lw_range
    EXPORTING
    #1 = 'A7'
    #2 = l_pos.
CALL METHOD OF p_lw_excel 'Range' = lw_range
    EXPORTING
    #1 = 'A8'
    #2 = l_pos. " + nik

CALL METHOD OF lw_range 'Autofilter'.

CALL METHOD OF lw_range 'INTERIOR' = lw_int.
SET PROPERTY OF lw_int 'ColorIndex' = 42.
SET PROPERTY OF lw_int 'Pattern' = 1.

CALL METHOD OF lw_range 'Borders' = lw_bor
EXPORTING
#1 = '7'.