merge cells

[spinkung]

Hi All,

I'm trying to merge some cells in excel from access vba. I've tried a few different ways now and nothing seems to work.

Here's what i've got...

xlSht1.Range("A2:C2").Merge
xlSht1.Range("A2:C2").MergeCells = True
xlSht1.Range("a2:C2").Merge Across:=True
xlSht1.Range(xlSht1.Cells(2, 1), xlSht1.Cells(2, 3)).Merge

... but when i open the wb the cells are not merged??

anyone??


Thanks,
Spin.

 

[Rabbie]

Need some more info from you. Have you saved the Excel file after you ran that code. If not you need to do so.

 

[spinkung]

I use the following code to output a table then format the sheet. it's just an example.

when i use the merge part in excel vba of the wb it's fine but when i bring it across to access vba it doesn't merge.

Public Sub output_to_excel()

'create file path
    Dim XLpth As String
        XLpth = "D:\Documents and Settings\GFrewin\Desktop\test.xls"

' ****************************************************************************************
' check file is locked
    Call IsFileLocked(XLpth)

' check file exists, if yes delete existing file
        FileSystem.Dir XLpth
        If XLpth = XLpth Then
            FileSystem.Kill XLpth
        End If

' ****************************************************************************************
' output to sheet1
        DoCmd.OutputTo acOutputTable, "table1", acFormatXLS, XLpth
' output to extra sheets
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "table1", XLpth


' ****************************************************************************************
' format excel file - requires the the ref : Microsoft Excel 12.0 Object Library
        Set xlObj = CreateObject("Excel.Application")
        Set xlApp = xlObj.Application
        Set xlWb = xlApp.Workbooks.Open(XLpth)
        Set xlSht1 = xlWb.Sheets("table1")
        Set xlSht2 = xlWb.Sheets("table11")

        ' format sheet1
            With xlSht1
                    xlSht1.Rows("1:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                    xlSht1.Range("A1").Value = "Hello"
                    
[COLOR="Red"]                    xlSht1.Range("A2:C2").Merge
                    xlSht1.Range("A2:C2").MergeCells = True
                    xlSht1.Range("a2:C2").Merge Across:=True[/COLOR]

                    xlSht1.Name = "Hello"
                    xlSht1.Cells.EntireColumn.AutoFit
            End With

        ' format sheet2
            With xlSht2
                    xlSht2.Name = "Goodbye"
                    xlSht2.Cells.EntireColumn.AutoFit
            End With

' Save and close Excel File
    xlWb.Save
    xlWb.Close
' Clear App/Obj
    xlApp.Quit
    Set xlObj = Nothing
    
OpenSpecific_xlFile (XLpth)

End Sub