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