Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Losing "merge-cells" formatting when populating Worksheet from VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Losing "merge-cells" formatting when populating Worksheet from VBA
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Center across selection" rather than "Merge cells" | Excel Discussion (Misc queries) | |||
is it possible to "merge" multiple worksheets into one worksheet? | Excel Discussion (Misc queries) | |||
Can you merge cells in a "list"? | Excel Worksheet Functions | |||
merge cells marco "help" | Excel Programming | |||
merge cells marco "help" | Excel Programming |