Excel Copy and Paste losing Merged Cells

[ndornacker]

When I perform the following from progress:
hSHEET2:Range("A1:BI10"):Select.
hSHEET2:Selection:Copy.
hSHEET1:Range("A1"):Select.
hSHEET1aste.

I lose the merged cells I have in "A3:I3"

How can I keep my merged cells... merged?

 

[Rob Fitzpatrick]

Merged cells pave the road to Excel misery.

 

[Stefan]

Which is exactly what Excel 2010 also does when you copy merged cells and paste them over themselves.

Create a workbook with

A1 B1 C1
A2 merged

In which merged is the result of merging B2 and C2. Then copy them and paste into A1 - bye bye merge...

 

[Rob Fitzpatrick]

If I try to reproduce your copy/paste manually in Excel, it works fine. The merged cells A3:I3 get copied to the new worksheet.

If I translate your code into VBA and run it in the VBE, it throws a runtime error (1004) because you are trying to interact with an object that is not active.

The following VBA stub works for me in Excel:

Sub foo()
    Sheets("sheet2").Range("A1:BI10").Copy
    Sheets("sheet1").Activate
    Sheets("sheet1").Range("A1").Select
    Sheets("sheet1").Paste
End Sub

So, translating to ABL:

define variable chexcel      as com-handle no-undo.
define variable chworkbook   as com-handle no-undo.
define variable chworksheet1 as com-handle no-undo.
define variable chworksheet2 as com-handle no-undo.

create "excel.application" chexcel no-error.

assign chexcel:visible = true
       chworkbook      = chexcel:workbooks:add()
       chworksheet1    = chexcel:sheets:item(1)
       chworksheet2    = chexcel:sheets:add(,,1).

/* populate the merged cell */
chworksheet1:range("a3:i3"):merge.
chworksheet1:range("a3"):value = "this is a value in a merged cell".

/* copy the data to the second worksheet */
chworksheet1:range("a1:bi10"):copy.
chworksheet2:activate.
chworksheet2:range("a1"):select.
chworksheet2:paste.

release object chworksheet1.
release object chworksheet2.
release object chworkbook.
release object chexcel.

I still think you should avoid merged cells if you can. They're a huge pain.

 

[mrobles]

Hi.

You are using 2 sheets.
I use something like this to walk between them
chWorkSheet:Range('A1'):Value = 'HOLA'.
chWorkSheet:Range('J10'):Value = 'ADIOS'.
chWorksheet:range('A1:J10'):SELECT.
chExcelApplication:SELECTION:COPY.
chWorksheet:range('A1'):SELECT.

chExcelApplication:Sheets:Item(2):SELECT.
chWorkSheet = chExcelApplication:Sheets:ITEM(2).
chWorksheet:range('B2'):SELECT.
chExcelApplication:ActiveSheetaste.

chWorksheet:range('A1'):SELECT.
chExcelApplication:CutCopyMode = FALSE.
MRobles

 

[Stefan]

If I try to reproduce your copy/paste manually in Excel, it works fine. The merged cells A3:I3 get copied to the new worksheet.

Ouch, I skipped over the two sheets bit...

I still think you should avoid merged cells if you can. They're a huge pain.

Damn users... ;-)