Solved: OLE

Former Member · ‎07-23-2008

HI

i want write program that will work with excel file using ole methods.

can anybody give me links to available books, articles, materials about

OLE methods, properties?

Former Member · ‎07-23-2008

Hi Marat,

Try the following link for detailed information on OLE methods and other related information.

[http://help.sap.com/saphelp_nw04/helpdata/en/e9/0be775408e11d1893b0000e8323c4f/frameset.htm]

This is part of SAP Desktop Office Integration which has other methods to integrate with other desktop applications and error handling.

[http://help.sap.com/saphelp_nw04/helpdata/en/e9/0be775408e11d1893b0000e8323c4f/frameset.htm]

Former Member · ‎07-23-2008

hi check this..

Report ZMULTICOLOR_TEST no standard page heading.

this report demonstrates how to send some ABAP data to an
EXCEL sheet using OLE automation.

include ole2incl.

handles for OLE objects

data: h_excel type ole2_object, " Excel object

h_mapl type ole2_object, " list of workbooks

h_map type ole2_object, " workbook

h_zl type ole2_object, " cell

h_f type ole2_object, " font

h_c type ole2_object. " color

DATA: FILENAME LIKE RLGRAP-FILENAME.

tables: spfli.

data h type i.

table of flights

data: it_spfli like spfli occurs 10 with header line.

&----

*& Event START-OF-SELECTION

&----

start-of-selection.

read flights

select * from spfli into table it_spfli.

display header

uline (61).

write: / sy-vline no-gap,

(3) 'Flg'(001) color col_heading no-gap, sy-vline no-gap,

(4) 'Nr'(002) color col_heading no-gap, sy-vline no-gap,

(20) 'Von'(003) color col_heading no-gap, sy-vline no-gap,

(20) 'Nach'(004) color col_heading no-gap, sy-vline no-gap,

(8) 'Zeit'(005) color col_heading no-gap, sy-vline no-gap.

uline /(61).

display flights

loop at it_spfli.

write: / sy-vline no-gap,

it_spfli-carrid color col_key no-gap, sy-vline no-gap,

it_spfli-connid color col_normal no-gap, sy-vline no-gap,

it_spfli-cityfrom color col_normal no-gap, sy-vline no-gap,

it_spfli-cityto color col_normal no-gap, sy-vline no-gap,

it_spfli-deptime color col_normal no-gap, sy-vline no-gap.

endloop.

uline /(61).

tell user what is going on

call function 'SAPGUI_PROGRESS_INDICATOR'

exporting

PERCENTAGE = 0

text = text-007

exceptions

others = 1.

start Excel

create object h_excel 'EXCEL.APPLICATION'.

PERFORM ERR_HDL.

set property of h_excel 'Visible' = 1.

CALL METHOD OF H_EXCEL 'FILESAVEAS' EXPORTING #1 = 'c:\kis_excel.xls' .

PERFORM ERR_HDL.
tell user what is going on

call function 'SAPGUI_PROGRESS_INDICATOR'

exporting

PERCENTAGE = 0

text = text-008

exceptions

others = 1.

get list of workbooks, initially empty

call method of h_excel 'Workbooks' = h_mapl.

perform err_hdl.

add a new workbook

call method of h_mapl 'Add' = h_map.

perform err_hdl.

tell user what is going on

call function 'SAPGUI_PROGRESS_INDICATOR'

exporting

PERCENTAGE = 0

text = text-009

exceptions

others = 1.

output column headings to active Excel sheet

perform fill_cell using 1 1 1 200 'Carrier id'(001).

perform fill_cell using 1 2 1 200 'Connection id'(002).

perform fill_cell using 1 3 1 200 'City from'(003).

perform fill_cell using 1 4 1 200 'City to'(004).

perform fill_cell using 1 5 1 200 'Dep. Time'(005).

loop at it_spfli.

copy flights to active EXCEL sheet

h = sy-tabix + 1.

if it_spfli-carrid cs 'AA'.

perform fill_cell using h 1 0 000255000 it_spfli-carrid.

elseif it_spfli-carrid cs 'AZ'.

perform fill_cell using h 1 0 168000000 it_spfli-carrid.

elseif it_spfli-carrid cs 'JL'.

perform fill_cell using h 1 0 168168000 it_spfli-carrid.

elseif it_spfli-carrid cs 'LH'.

perform fill_cell using h 1 0 111111111 it_spfli-carrid.

elseif it_spfli-carrid cs 'SQ'.

perform fill_cell using h 1 0 100100100 it_spfli-carrid.

else.

perform fill_cell using h 1 0 000145000 it_spfli-carrid.

endif.

if it_spfli-connid lt 400.

perform fill_cell using h 2 0 255000255 it_spfli-connid.

elseif it_spfli-connid lt 800.

perform fill_cell using h 2 0 077099088 it_spfli-connid.

else.

perform fill_cell using h 2 0 246156138 it_spfli-connid.

endif.

if it_spfli-cityfrom cp 'S*'.

perform fill_cell using h 3 0 155155155 it_spfli-cityfrom.

elseif it_spfli-cityfrom cp 'N*'.

perform fill_cell using h 3 0 189111222 it_spfli-cityfrom.

else.

perform fill_cell using h 3 0 111230222 it_spfli-cityfrom.

endif.

if it_spfli-cityto cp 'S*'.

perform fill_cell using h 4 0 200200200 it_spfli-cityto.

elseif it_spfli-cityto cp 'N*'.

perform fill_cell using h 4 0 000111222 it_spfli-cityto.

else.

perform fill_cell using h 4 0 130230230 it_spfli-cityto.

endif.

if it_spfli-deptime lt '020000'.

perform fill_cell using h 5 0 145145145 it_spfli-deptime.

elseif it_spfli-deptime lt '120000' .

perform fill_cell using h 5 0 015215205 it_spfli-deptime.

elseif it_spfli-deptime lt '180000' .

perform fill_cell using h 5 0 000215205 it_spfli-deptime.

else.

perform fill_cell using h 5 0 115115105 it_spfli-deptime.

endif.

endloop.

EXCEL FILENAME

CONCATENATE SY-REPID '_' SY-DATUM6(2) '_' SY-DATUM4(2) '_'

SY-DATUM(4) '_' SY-UZEIT '.XLS' INTO FILENAME.

CALL METHOD OF H_MAP 'SAVEAS' EXPORTING #1 = FILENAME.

free object h_excel.

perform err_hdl.

----

FORM FILL_CELL *

----

sets cell at coordinates i,j to value val boldtype bold *

----

form fill_cell using i j bold col val.

call method of h_excel 'Cells' = h_zl

exporting

#1 = i

#2 = j.

perform err_hdl.

set property of h_zl 'Value' = val .

perform err_hdl.

get property of h_zl 'Font' = h_f.

perform err_hdl.

set property of h_f 'Bold' = bold .

perform err_hdl.

set property of h_f 'Color' = col.

perform err_hdl.

endform. "FILL_CELL

&----

*& Form ERR_HDL

&----

outputs OLE error if any *

----

--> p1 text
<-- p2 text

----

form err_hdl.

if sy-subrc <> 0.

write: / 'OLE-Automation Error:'(010), sy-subrc.

stop.

endif.

endform. " ERR_HDL

Former Member · ‎07-23-2008

Hi,

check this link ,

http://help.sap.com/saphelp_46c/helpdata/en/db/9987b3c3cf11d194ad00a0c94260a5/content.htm

cheers,

sowjanya.

former_member705122 · ‎07-23-2008

Hi,

Check this threads:

http://www.sap-img.com/abap/uploading-multiple-multitab-excel-sheets-or-ranges.htm

http://searchsap.techtarget.com/tip/1,289483,sid21_gci775513,00.html#

Regards

Adil

Former Member · ‎07-23-2008

Hi,

I have worked on OLE earlier and found this links to be useful .

In cae you dont get your reply you can revert back with your query.

http://help.sap.com/printdocu/core/Print46c/en/data/pdf/BCFESDE6/BCFESDE6.pdf

http://www.sapfans.com/forums/viewtopic.php?f=13&t=309588

Regards,

Nisrin.

former_member188685 · ‎07-23-2008

Check the sample code.

REPORT zdownload_excel_multi .
*
INCLUDE ole2incl.
DATA w_index TYPE i.
DATA:
  application TYPE ole2_object,
  workbook    TYPE ole2_object,
  sheet       TYPE ole2_object,
  cells       TYPE ole2_object,
  sheet1      TYPE ole2_object,
  sheets      TYPE ole2_object.

CONSTANTS:
  row_max TYPE i VALUE 256.

DATA:
  index TYPE i,
  det   TYPE REF TO cl_abap_structdescr,
  wa    LIKE LINE OF det->components.

DATA:
  BEGIN OF i_finaltab OCCURS 0,
    col1 TYPE i,
    col2 TYPE i,
    col3 TYPE i,
    col4 TYPE i,
  END OF i_finaltab.

DATA :
  count TYPE i,
  v_excel_count(3), " type i value 1,
  sheet_name(15) .

***********************************************************************
*START-OF-SELECTION
***********************************************************************
START-OF-SELECTION.


  DO 15 TIMES.
    count = count + 1.
    i_finaltab-col2 = 5 * count.
    i_finaltab-col3 = 10 * count.
    i_finaltab-col4 = 20 * count.
    MOVE : count TO i_finaltab-col1.
    APPEND i_finaltab.
    CLEAR i_finaltab.
  ENDDO.

  CLEAR count.

  CREATE OBJECT application 'excel.application'.
  SET PROPERTY OF application 'visible' = 1.
  CALL METHOD OF application 'Workbooks' = workbook.
  CALL METHOD OF workbook 'Add'.


*Create first Excel Sheet
  w_index = w_index + 1.
  CALL METHOD OF application 'Worksheets' = sheet
    EXPORTING
    #1 = w_index.
  CALL METHOD OF sheet 'Activate'.
  SET PROPERTY OF sheet 'Name' = 'EXCEL0'.

  count = 1.
  index = row_max * ( count - 1 ) + 1.
  PERFORM header_details.

  LOOP AT i_finaltab.

    count = count + 1.

    IF count LE 6.

      index = row_max * ( count - 1 ) + 1. " 1 - column name

      CALL METHOD OF sheet 'Cells' = cells
        EXPORTING
        #1 = index.

      SET PROPERTY OF cells 'Value' = i_finaltab-col1.

      index = index + 1.

      CALL METHOD OF sheet 'Cells' = cells
        EXPORTING
        #1 = index.
      SET PROPERTY OF cells 'Value' = i_finaltab-col2.

      index = index + 1.

      CALL METHOD OF sheet 'Cells' = cells
        EXPORTING
        #1 = index.
      SET PROPERTY OF cells 'Value' = i_finaltab-col3.

      index = index + 1.

      CALL METHOD OF sheet 'Cells' = cells
        EXPORTING
        #1 = index.
      SET PROPERTY OF cells 'Value' = i_finaltab-col4.

    ELSE.
      v_excel_count = v_excel_count + 1.

      CONCATENATE 'EXCEL' v_excel_count INTO sheet_name.

      CLEAR count.
      count = count + 1.

      w_index = w_index + 1.
      CALL METHOD OF application 'Worksheets' = sheet
        EXPORTING
        #1 = w_index.
      SET PROPERTY OF sheet 'Name' = sheet_name.
      CALL METHOD OF sheet 'Activate'.

      index = row_max * ( count - 1 ) + 1.

      PERFORM header_details.

      count = count + 1.

      index = row_max * ( count - 1 ) + 1. " 1 - column name
      CALL METHOD OF sheet 'Cells' = cells
        EXPORTING
        #1 = index.
      SET PROPERTY OF cells 'Value' = i_finaltab-col1.

      index = index + 1.

      CALL METHOD OF sheet 'Cells' = cells
        EXPORTING
        #1 = index.
      SET PROPERTY OF cells 'Value' = i_finaltab-col2.

      index = index + 1.

      CALL METHOD OF sheet 'Cells' = cells
        EXPORTING
        #1 = index.
      SET PROPERTY OF cells 'Value' = i_finaltab-col3.

      index = index + 1.

      CALL METHOD OF sheet 'Cells' = cells
        EXPORTING
        #1 = index.
      SET PROPERTY OF cells 'Value' = i_finaltab-col4.


    ENDIF.
  ENDLOOP.

FORM header_details.

  det ?= cl_abap_typedescr=>describe_by_data( i_finaltab ).

  LOOP AT det->components INTO wa.
    CALL METHOD OF sheet 'Cells' = cells
      EXPORTING
      #1 = index.
    SET PROPERTY OF cells 'Value' = wa-name.
    index = index + 1.
  ENDLOOP.

ENDFORM. " header_details

Former Member · ‎07-23-2008

hiii

use following code for OLE

REPORT  YOLE_FORMATING_EXCEL.
* OLE2 include
INCLUDE ole2incl.

* OLE2 objects.
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.

* Internal table for the flight information.
DATA:
  t_material LIKE STANDARD TABLE
               OF makt
             WITH HEADER LINE
          INITIAL SIZE 0.

* Work Variables declaration.
DATA:
  W_CHAR(2)  TYPE C,                   " Holds cell value
  w_cell1(3) TYPE c,                   " Holds cell value
  w_cell2(3) TYPE c,                   " Holds cell value
  w_row_num  TYPE i VALUE 1,           " Row Number.
  w_col_num  TYPE i VALUE 1.           " Column Number.

START-OF-SELECTION.
* Selecting the flight information from spfli table.
  SELECT *
    FROM makt
    INTO TABLE t_material
      up to 10 rows.

* Creating the excel application object.
  CREATE OBJECT excel 'EXCEL.APPLICATION'.

* Creating work book
  CALL METHOD OF excel 'WORKBOOKS' = workbook.

* Adding a book to the workbook
  CALL METHOD OF workbook 'ADD' = book.

* Merging third(C1), fouth(D1) cells of row one.
  CALL METHOD OF excel 'range' = range
    EXPORTING
      #1 = 'B1'
      #2 = 'D1'.
  CALL METHOD OF range 'merge'.

* Making the book added as the active sheet and setting its property  to
* visible mode
  CALL METHOD OF book 'ACTIVESHEET' = activesheet.
  SET PROPERTY OF excel 'VISIBLE' = 1.

* Writing header informaion to the excel sheet.
  PERFORM fill_heading.
* Displaying the material information in the active sheet.
  PERFORM data_display.

*&---------------------------------------------------------------------*
*&      Form  FILL_HEADING                                             *
*&---------------------------------------------------------------------*
*  This subroutine will fill heading in the excel sheet for the data to*
*  to be displayed.                                                    *
*----------------------------------------------------------------------*
* No interface parameters exist.                                       *
*----------------------------------------------------------------------*
FORM fill_heading .
* Set row and column positions for each cell
  DO 2 TIMES.
    CALL METHOD OF excel 'CELLS' = cell
      EXPORTING
        #1 = 1
        #2 = sy-index.
    IF sy-index EQ 1.
* Set properties of cell for material.
      SET PROPERTY OF cell 'VALUE'               = 'MATNR'.
      SET PROPERTY OF cell 'HorizontalAlignment' =  -4108.
      SET PROPERTY OF cell 'ColumnWidth'         = 12.
      GET PROPERTY OF cell 'FONT'                = bold.
      SET PROPERTY OF bold 'BOLD'                = 1 .
      SET PROPERTY OF bold 'size'                = 10.
      GET PROPERTY OF cell 'Interior'            = interior.
      SET PROPERTY OF interior 'Color'           = '55000'.
    ELSE.
* Set properties of cell for material description.
      SET PROPERTY OF cell 'VALUE'               = 'MAKTX'.
      SET PROPERTY OF cell 'HorizontalAlignment' =  -4108.
      SET PROPERTY OF cell 'ColumnWidth'         = 12.
      GET PROPERTY OF cell 'FONT'                = bold.
      SET PROPERTY OF bold 'BOLD'                = 1 .
      SET PROPERTY OF bold 'size'                = 10.
      GET PROPERTY OF cell 'Interior'            = interior.
      SET PROPERTY OF interior 'Color'           = '55000'.
    ENDIF.
  ENDDO.
ENDFORM.                               " FILL_HEADING
*&---------------------------------------------------------------------*
*&      Form  DATA_DISPLAY                                             *
*&---------------------------------------------------------------------*
*  This subroutine will display flight information in the excel sheet. *
*----------------------------------------------------------------------*
* No interface parameters exist.                                       *
*----------------------------------------------------------------------*
FORM data_display .
  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.

ENDFORM.                    "data_display

regards

twinkal

Former Member · ‎07-23-2008

Hi Marat,

Try the following link for detailed information on OLE methods and other related information.

[http://help.sap.com/saphelp_nw04/helpdata/en/e9/0be775408e11d1893b0000e8323c4f/frameset.htm]

This is part of SAP Desktop Office Integration which has other methods to integrate with other desktop applications and error handling.

[http://help.sap.com/saphelp_nw04/helpdata/en/e9/0be775408e11d1893b0000e8323c4f/frameset.htm]