如何判断工作簿查询的刷新操作是否失败

要实现批量刷新所有数据连接后统计成功/失败情况，核心是通过同步刷新+错误捕获来记录每个连接的状态，具体方案如下：

核心思路 #

• 遍历工作簿中所有WorkbookConnection对象，过滤出可刷新的数据连接（排除图表、切片器这类非查询连接）
• 将连接的BackgroundQuery属性设为False，强制同步刷新（这样刷新过程会阻塞代码执行，直到完成，才能准确捕获错误）
• 用错误捕获机制记录刷新失败的连接名称和错误信息
• 最后汇总结果，弹窗告知用户成功/失败数量及失败列表，再让用户选择继续或取消操作

完整VBA代码 #

Sub RefreshAllConnectionsAndCheckStatus()
    Dim conn As WorkbookConnection
    Dim successCount As Integer
    Dim failCount As Integer
    Dim failList As String
    Dim userChoice As VbMsgBoxResult
    
    ' 初始化统计变量
    successCount = 0
    failCount = 0
    failList = ""
    
    ' 遍历所有工作簿连接
    For Each conn In ThisWorkbook.Connections
        ' 只处理可刷新的数据连接类型（可根据实际情况调整）
        Select Case conn.Type
            Case xlConnectionTypeOLEDB, xlConnectionTypeODBC, xlConnectionTypeText, xlConnectionTypeWeb, xlConnectionTypePowerQuery
                On Error Resume Next ' 开启错误捕获
                ' 关闭后台刷新，确保同步执行
                If conn.Type = xlConnectionTypePowerQuery Then
                    conn.PowerQueryConnection.BackgroundQuery = False
                Else
                    conn.OLEDBConnection.BackgroundQuery = False
                End If
                conn.Refresh ' 执行刷新
                
                ' 判断是否刷新失败
                If Err.Number <> 0 Then
                    failCount = failCount + 1
                    failList = failList & "- " & conn.Name & "（错误：" & Err.Description & "）" & vbCrLf
                    Err.Clear ' 清除错误状态
                Else
                    successCount = successCount + 1
                End If
                On Error GoTo 0 ' 关闭错误捕获
        End Select
    Next conn
    
    ' 生成结果提示文本
    Dim resultMsg As String
    resultMsg = "刷新完成：" & vbCrLf & _
                "成功刷新：" & successCount & "个查询" & vbCrLf & _
                "刷新失败：" & failCount & "个查询" & vbCrLf
    If failCount > 0 Then
        resultMsg = resultMsg & vbCrLf & "失败的查询列表：" & vbCrLf & failList
    End If
    
    ' 弹窗提示并获取用户选择
    userChoice = MsgBox(resultMsg & vbCrLf & "是否继续操作？", vbYesNo + vbInformation, "刷新结果")
    
    ' 根据用户选择执行后续逻辑
    If userChoice = vbYes Then
        ' 此处写入用户选择继续后的操作代码
        MsgBox "将继续执行后续流程"
    Else
        ' 此处写入用户选择取消后的操作代码
        MsgBox "操作已取消"
    End If
End Sub

注意事项 #

• 代码中过滤的连接类型可根据你的实际场景调整，比如新增其他类型的查询连接
• 同步刷新会暂时阻塞Excel，若有大数据量查询，可能导致Excel短时间无响应，需提前告知用户

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