如何删除Excel VBA中指定Power Query（RSPL_Query）的关联连接？

问题分析 #

你遇到的运行时错误1004，主要原因有两个：

• 遍历Connections集合时直接删除元素，会破坏集合的遍历顺序，引发对象引用异常
• Power Query生成的连接不一定是ODBC类型，仅判断xlConnectionTypeODBC会遗漏目标连接

修正后的代码 #

Sub CleanRSPLQueryAndConnections()
    Dim wb As Workbook
    Dim targetQuery As WorkbookQuery
    Dim connToDelete As Collection
    Dim cn As WorkbookConnection
    
    Set wb = ActiveWorkbook
    Set connToDelete = New Collection
    
    ' 定位名为RSPL_Query的查询
    On Error Resume Next
    Set targetQuery = wb.Queries("RSPL_Query")
    On Error GoTo 0
    
    If targetQuery Is Nothing Then
        MsgBox "未找到名为RSPL_Query的查询", vbInformation
        Exit Sub
    End If
    
    ' 收集该查询关联的所有连接
    For Each cn In wb.Connections
        ' 匹配Power Query常用的OLEDB连接
        If cn.Type = xlConnectionTypeOLEDB Then
            If InStr(1, cn.OLEDBConnection.CommandText, targetQuery.Name, vbTextCompare) > 0 Then
                connToDelete.Add cn
            End If
        ' 兼容ODBC类型连接
        ElseIf cn.Type = xlConnectionTypeODBC Then
            If InStr(1, cn.ODBCConnection.Connection, targetQuery.Name, vbTextCompare) > 0 Then
                connToDelete.Add cn
            End If
        End If
    Next cn
    
    ' 倒序删除连接，避免遍历集合时的索引异常
    Dim i As Integer
    For i = connToDelete.Count To 1 Step -1
        On Error Resume Next
        connToDelete(i).Delete
        On Error GoTo 0
    Next i
    
    ' 删除目标查询
    On Error Resume Next
    targetQuery.Delete
    On Error GoTo 0
    
    MsgBox "RSPL_Query及其关联连接已清理完成", vbInformation
End Sub

代码说明 #

• 精准定位查询：通过wb.Queries("RSPL_Query")直接锁定目标查询，彻底避免误删其他查询或连接
• 先收集后删除：先将关联连接存入集合，再统一删除，规避遍历集合时直接删除元素引发的报错
• 兼容多连接类型：同时处理Power Query常见的OLEDB和ODBC连接类型，覆盖更多使用场景
• 容错处理：加入错误捕获语句，防止因连接已被删除或不存在导致的运行中断

使用注意事项 #

• 运行宏时确保目标工作簿处于激活状态
• 作为加载项使用时，若目标工作簿无RSPL_Query查询，宏会弹出提示并终止
• 运行前建议保存工作簿，避免意外数据丢失

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