Excel 2007:“自动化错误"使用 VBA 对数据进行排序时 (80010105) [英] Excel 2007: "Automation Error" when sorting data using VBA (80010105)

问题描述

我正在从 Excel 文件运行 VBA 脚本，该脚本打开另一个文件、操作数据和一些图表，然后保存它.一切都完美无缺，除非我尝试对数据进行排序.当我到达 .SortFields.Add Key:=Range("J3:J11")... 行时，出现错误

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

我确定这与我引用 Excel 对象的方式有关，但我已经尝试了所有方法，但似乎找不到解决方案.排序代码是从宏记录器中借用并修改的.

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

推荐答案

问题是您没有正确引用您的范围.您使用的排序代码是为在当前 Excel 实例中对活动工作表上的范围进行排序而编写的.

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.

解决此问题的最简单方法是将范围引用为在另一个 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")

不过，我的建议是改用 Workbook 和 Worksheet 对象.

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

这篇关于Excel 2007:“自动化错误"使用 VBA 对数据进行排序时 (80010105)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！