Losing "merge-cells" formatting when populating Worksheet from VBA

[Cardio]

I have an Access application that is programmatically populating an
Excel spreadsheet via OLE (i.e. dim objXL as Excel.Application, set
objXl=CreateObject("excel.application") etc etc)

The application opens an existing xls file and then does a SaveAs so
the target is saved with a name reflecting it's contents e.g.
template=ApplicationTemplate.xls and the SaveAs destination is
Application123456.xls meaning data for application 123456 is contained
within it

When I go to populate a cell via something like a
"MyRange.Cells(a,b).Value = MyValue", the resulting spreadsheet has the
value OK but if that cell happened to have the "MergeCells" property set
i.e. it was merged with the 4 adjacent columns then this property has
been lost and I'm back to a single cell...

Is there a way I can programaticaly detect the merge cells properties
and then restore them or alternatively can I use the clipboard and do a
PasteSpecial for just the values in some way?

Any help or advice is appreciated

Cheers

 

[Dave Peterson]

I couldn't duplicate this in xl2003. I used this (in the same instance of
excel):

Option Explicit
Sub testme()
Dim myRng As Range
Set myRng = ActiveSheet.Range("a1")
myRng.MergeArea.UnMerge
myRng.Resize(1, 5).Merge
Debug.Print ActiveSheet.Range("a1").MergeArea.Address & "<--before"
myRng.Value = "myValue"
Debug.Print ActiveSheet.Range("a1").MergeArea.Address & "<--after"
End Sub

I got this back in the immediate window:
$A$1:$E$1<--before
$A$1:$E$1<--after

And when I swapped back to excel A1:E1 were merged.

======
But a suggestion for a workaround--keep track of the range.

Option Explicit
Sub testme2()

Dim myRng As Range
Dim mySavedRng As Range

Set myRng = ActiveSheet.Range("a1")
myRng.MergeArea.UnMerge

myRng.Resize(1, 5).Merge

Set mySavedRng = myRng.MergeArea

'even if I do it myself
myRng.MergeArea.UnMerge
myRng.Value = "myValue"
'reapply the merge
mySavedRng.Merge
End Sub