topic Re: cell merging using sap automation object excel in Application Development and Automation Discussions

cell merging using sap automation object excel

Former Member — Tue, 07 May 2013 09:35:04 GMT

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

Re: cell merging using sap automation object excel

tolga_polat — Tue, 07 May 2013 19:07:39 GMT

Hi Fabrizio,

Check this document for Excel OLE

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

Thanks to Serdar Şimşekler.

Regards

Tolga

Re: cell merging using sap automation object excel

juan_suros — Tue, 07 May 2013 23:04:57 GMT

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'.

Re: cell merging using sap automation object excel

Former Member — Wed, 08 May 2013 05:35:02 GMT

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

Re: cell merging using sap automation object excel

rajashree_palkar — Tue, 07 Jul 2020 10:52:45 GMT

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.