跨文件排序时触发运行时错误2147023170 (800706be)求助

问题场景与错误 #

有一个存储员工假期信息的数据库文件，清理名单中部分姓名后执行排序操作时出错：VBA代码运行到.SetRange Range("V:W")行时，触发运行时错误'2147023170 (800706be)'：Automation error，远程过程调用失败。

原代码 #

Option Explicit

Sub clear_holiday()
Dim wn As Worksheet
Dim ws As Worksheet
Dim i As Long
Dim name As String
Dim wDate As Date
Dim holiday As Double
Dim Rlastrow As Long

Application.ScreenUpdating = False

open_wb_onedrive 'opens the document where the database is.
Set ws = openwb.Worksheets("data") 'assigns the sheet to a variable

With ws

    .Unprotect Password:=pass
    i = 2
    
    Do Until .Cells(i, 22).value = "" 'go through all the values in the date column
    
        name = Trim(.Cells(i, 22).value) 'remove blank spaces and get employees name
        wDate = .Cells(i, 23).value 'retrieves date of the holiday
        
        .Activate
        Rlastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
        
        If wDate <= Date Then 'if the persons holiday is in the past we update the data
            
            'updates employees data on the table
            holiday = FirstPartMatch(name, .Range("A1:A" & Rlastrow))
            .Cells(holiday, 1).value = name
            
            'clears the data from holiday column
            .Cells(i, 22).value = ""
            .Cells(i, 23).value = ""
        
        End If
        
        i = i + 1
        
    Loop
    
    'Sorts holiday information removing blank rows
    .Activate
    openwb.Worksheets("data").Columns("V:W").Select
    openwb.Worksheets("data").Sort.SortFields.Clear
    openwb.Worksheets("data").Sort.SortFields.Add2 Key:=Range("W1:W1000"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .SetRange Range("V:W")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    .Protect Password:=pass
    
End With

get_data ws
End Sub

问题排查 #

错误核心原因是对象引用混乱：

• 代码处于With ws块中，但排序操作混合使用了openwb.Worksheets("data")和未限定的Range，导致Excel无法明确识别操作的工作表对象，引发自动化调用冲突。
• 多余的.Activate操作改变了工作表焦点，进一步加剧对象归属的歧义。
• 排序整列V:W会处理大量空行，增加Excel运行负担，容易触发资源调用错误。

修复方案与代码 #

修复后的代码 #

Option Explicit

Sub clear_holiday()
    Dim ws As Worksheet
    Dim i As Long
    Dim name As String
    Dim wDate As Date
    Dim holiday As Double
    Dim Rlastrow As Long
    Dim lastHolidayRow As Long
    Dim sortRange As Range

    Application.ScreenUpdating = False
    Application.EnableEvents = False ' 禁用事件避免排序触发不必要回调

    open_wb_onedrive ' 打开数据库文档
    Set ws = openwb.Worksheets("data")

    With ws
        .Unprotect Password:=pass
        i = 2
        
        ' 获取假期数据的最后一行，避免无限循环
        lastHolidayRow = .Cells(.Rows.Count, 22).End(xlUp).Row
        
        Do While i <= lastHolidayRow
            name = Trim(.Cells(i, 22).Value)
            
            ' 增加日期有效性判断，避免无效值报错
            If IsDate(.Cells(i, 23).Value) Then
                wDate = .Cells(i, 23).Value
                
                Rlastrow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
                
                If wDate <= Date Then
                    holiday = FirstPartMatch(name, .Range("A1:A" & Rlastrow))
                    .Cells(holiday, 1).Value = name
                    
                    ' 清空数据用ClearContents更规范
                    .Cells(i, 22).ClearContents
                    .Cells(i, 23).ClearContents
                End If
            End If
            i = i + 1
        Loop
        
        ' 排序操作：所有对象均通过With块限定，消除歧义
        .Sort.SortFields.Clear
        
        ' 仅排序有数据的区域，减少处理量
        Set sortRange = .Range("V1:W" & lastHolidayRow)
        .Sort.SortFields.Add2 Key:=.Range("W1:W" & lastHolidayRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
        With .Sort
            .SetRange sortRange
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        .Protect Password:=pass
    End With

    get_data ws
    
    ' 恢复Excel设置
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

关键修改说明 #

• 统一对象引用：所有工作表操作均通过With ws块限定，不再混合使用openwb.Worksheets("data")，确保Excel明确操作目标。
• 移除.Activate：VBA操作工作表无需激活，该操作只会引发焦点混乱，增加出错概率。
• 缩小排序范围：仅排序有数据的区域，避免整列处理带来的性能问题和空行干扰。
• 增加有效性判断：对日期单元格做IsDate校验，避免无效值导致的运行错误。
• 禁用事件：排序前禁用Application.EnableEvents，防止触发工作表事件引发冲突，执行完成后恢复设置。

内容的提问来源于stack exchange，提问作者Believe82