Excel 2007: “Automation Error” when sorting data u

Question

I'm running a VBA script from an Excel file that opens another file, manipulates data and some charts, then saves it. Everything works perfectly except when I try to sort data. When I get to the line .SortFields.Add Key:=Range("J3:J11")... I get an error

    Run-time error '-2147417851 (80010105)':
    Automation error
    The server threw an exception

I'm sure it has something to do with the way I'm referencing the Excel object, but I've tried everything and can't seem to find a solution. The sorting code was borrowed from the macro recorder and modified.

Private Sub button1_Click()
Dim path As String
Dim exl As Excel.Application
path = ActiveWorkbook.path & "\"
Set exl = CreateObject("Excel.Application")

With exl
    .Workbooks.Open path & "bin\Integrated UPSIDE with Summary.xlsm"

    <...other code...>

    With .Worksheets("Summary").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("J3:J11") _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange Range("C2:P11")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    <...other code...>

.Workbooks.Close
End With
exl.QUIT
End Sub

Any suggestions are GREATLY appreciated! Thanks

Answer 1

The problem is you aren't correctly referencing your Ranges. The sort code you are using was written for sorting ranges on the active worksheet in the current instance of Excel.

The simplest way to fix this is to reference the ranges as being in the other instance of Excel.

.SortFields.Add Key:=exl.Worksheets("Summary").Range("J3:J11") _
              , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange exl.Worksheets("Summary").Range("C2:P11")

However, my suggestion would be to use the Workbook and Worksheet objects instead.

Private Sub button1_Click()
    Dim path As String
    Dim exl As Excel.Application
    Dim wbk As Workbook, sht As Worksheet
    path = ActiveWorkbook.path & "\"
    Set exl = CreateObject("Excel.Application")

    With exl
        Set wbk = .Workbooks.Open(path & "bin\Integrated UPSIDE with Summary.xlsm")

        '<...other code...>

        Set sht = wbk.Worksheets("Summary")
        With sht.Sort
            .SortFields.Clear
            .SortFields.Add Key:=sht.Range("J3:J11") _
                , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            .SetRange sht.Range("C2:P11")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

        '<...other code...>

        wbk.Close
        Set sht = Nothing
        Set wbk = Nothing
    End With
    exl.Quit
End Sub