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



Cardio wrote:
>
> 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
>
> --
> Cardio
> ------------------------------------------------------------------------
> Cardio's Profile: http://www.excelforum.com/member.php...o&userid=28976
> View this thread: http://www.excelforum.com/showthread...hreadid=487076

--

Dave Peterson