cell merging using sap automation object excel

[Former Member]

Hi ,

I am using SAP automation object in order to manipulate an excel template stored in business document object service (OAER). In principle everything is working as I supposed. I am able to manipulate cell contents from the template by replacing some link references with SAP infos. I am also able to add entire new sections by using several methods of i_oi_spreadsheet class interface.

The problem I'm facing is now related to the possibility to "select" a range of excel columns and merge them in a single column.

here an example of what I would like to obtain

Please note this "table" cannot be predefined and stored in the template because this contains unpredictable number of items. So I must create it in the runtime by using "insert range" and "insert single table" methods. Afterwards I would like con condense the first row of this table (the header) as a single column.

Is there a way to achieve it? Does someone have a sample coding in case it was possible?

Thanks in advance

Fabrizio

Header
Col 1 row1	Col2 row1	Col3 row1
Col 1 row2	Col 2 row 2	Col3 row2
Col1 row3	Col2 row 3	Col3 row 3

[Former Member]

Hi ,

I am using SAP automation object in order to manipulate an excel template stored in business document object service (OAER). In principle everything is working as I supposed. I am able to manipulate cell contents from the template by replacing some link references with SAP infos. I am also able to add entire new sections by using several methods of i_oi_spreadsheet class interface.

The problem I'm facing is now related to the possibility to "select" a range of excel columns and merge them in a single column.

here an example of what I would like to obtain

Please note this "table" cannot be predefined and stored in the template because this contains unpredictable number of items. So I must create it in the runtime by using "insert range" and "insert single table" methods. Afterwards I would like con condense the first row of this table (the header) as a single column.

Is there a way to achieve it? Does someone have a sample coding in case it was possible?

Thanks in advance

Fabrizio

Header
Col 1 row1	Col2 row1	Col3 row1
Col 1 row2	Col 2 row 2	Col3 row2
Col1 row3	Col2 row 3	Col3 row 3

[tolga_polat]

Hi Fabrizio,

Check this document for Excel OLE

http://scn.sap.com/docs/DOC-10373

Thanks to Serdar Şimşekler.

Regards

Tolga

[juan_suros]

In VBA, you would do this with two lines of code:

Excel.Application.Range("B1:B12").Select

Excel.Application.Selection.Merge

In ABAP using OLE automation it would be something like this:

DATA: oExcel TYPE ole2_object.

DATA: oRange TYPE ole2_object.

DATA: oSelect TYPE ole2_object.

CREATE OBJECT oExcel 'EXCEL.APPLICATION'.

CALL METHOD OF oExcel 'Range' = oRange EXPORTING #1 = 'B1:B12'.

CALL METHOD OF oRange 'Select'.

CALL METHOD OF oExcel 'Selection' = oSelect.

CALL METHOD OF oSelect 'Merge'.

[Former Member]

Hi Fabrizio,

Check this SAP Demo Program " RSDEMO01" , Create a 'Z' Copy of this Program and do some below changes in this "Z" Copy Program  and the changes are..

ADD TWO Variables

  DATA:    GS_FONT TYPE OLE2_OBJECT ,

               GV_LINE_CNTR TYPE I .

================================================================

HIDE THESE BELOW LINES ....IN YOUR "Z - Program "

* output column headings to active Excel sheet

   PERFORM FILL_CELL USING 1 1 1 'Flug'(001).      " hide this line

   PERFORM FILL_CELL USING 1 2 1 'Nr'(002).          " hide this line

   PERFORM FILL_CELL USING 1 3 1 'Von'(003).       " hide this line

   PERFORM FILL_CELL USING 1 4 1 'Nach'(004).      " hide this line

   PERFORM FILL_CELL USING 1 5 1 'Zeit'(005).       " hide this line

=======================================================================

ADD THIS LINE IN YOUR "Z-Program" AFTER HIDE ABOVE LINES.

perform HEADER .

FORM HEADER .

  GV_LINE_CNTR = 1 ."line counter

*----------------------------------------------------------------------*

*                TITLE                                                 *

*----------------------------------------------------------------------*

   CALL METHOD OF H_EXCEL 'Cells' = GS_CELL1

     EXPORTING

     #1 = 1

     #2 = 1.

   CALL METHOD OF H_EXCEL 'Cells' = GS_CELL2

     EXPORTING

     #1 = 1

     #2 = 5.

   CALL METHOD OF H_EXCEL 'Range' = GS_CELLS

     EXPORTING

     #1 = GS_CELL1

     #2 = GS_CELL2.

   CALL METHOD OF GS_CELLS 'Select' .

*--Merging

   CALL METHOD OF GS_CELLS 'Merge' .

*--Setting title data

   CALL METHOD OF H_EXCEL 'Cells' = GS_CELL1

     EXPORTING

     #1 = GV_LINE_CNTR

     #2 = 1.

   SET PROPERTY OF GS_CELL1 'Value' = 'Header Name' .

*--Formatting the title

   GET PROPERTY OF GS_CELL1 'Font' = GS_FONT .

   SET PROPERTY OF GS_FONT 'Underline' = 2 .

   SET PROPERTY OF GS_FONT 'Bold' = 1 .

ENDFORM.                    " HEADER

===================================================================

ADD ONE PERFORM BEFORE THIS LINE "FREE OBJECT H_EXCEL." .. LINE NUMBER 89

PERFORM AUTO_FILTER .

FORM AUTO_FILTER .

CALL METHOD OF H_EXCEL 'Cells' = GS_CELL1

      EXPORTING

      #1 = 1                                   " 1st Row

      #2 = 1.                                  " 1st Column

    CALL METHOD OF H_EXCEL 'Cells' = GS_CELL2

      EXPORTING

      #1 = 5                                      "  5th Row

      #2 = 5.                                     "  4th Column

*

    CALL METHOD OF H_EXCEL 'Range' = GS_CELLS         " Select range Area

      EXPORTING

      #1 = GS_CELL1

      #2 = GS_CELL2.

   ENDFORM.                    " AUTO_FILTER

======================================================================

Output :

Regard's

Smruti

[rajashree_palkar]

Hello!!

Can you please help me to convert belo Macro into OLE format.

ActiveSheet.CheckBoxes.Add(180, 123.5, 33.5, 17.5).Select

Range("B10").Select

Thank you,

Rajashree Palkar.