如何通过VBA实现Power Query连接刷新失败时的提示功能？

原代码的问题在于使用On Error Resume Next忽略所有错误，且无论刷新结果如何都固定弹出"Refresh Complete"。以下是修改后的代码，能捕获刷新过程中的错误并显示对应信息：

'Worksheets("Details").Unprotect
Dim Connection As WorkbookConnection
Dim bugfix As Integer
Dim hasError As Boolean
Dim errorMsg As String

hasError = False
errorMsg = ""

For bugfix = 1 To 2
    For Each Connection In ActiveWorkbook.Connections
        With Connection
            If .Type = xlConnectionTypeODBC Then
                .ODBCConnection.BackgroundQuery = False
            ElseIf .Type = xlConnectionTypeOLEDB Then
                .OLEDBConnection.BackgroundQuery = False
            End If
        End With
        
        ' 针对当前连接刷新操作捕获错误
        On Error Resume Next
        Connection.Refresh
        If Err.Number <> 0 Then
            hasError = True
            errorMsg = errorMsg & "连接 [" & Connection.Name & "] 刷新失败：" & Err.Description & vbCrLf
            Err.Clear ' 清除错误状态，避免影响后续连接
        End If
        On Error GoTo 0 ' 恢复默认错误处理
    Next Connection
Next bugfix

'Worksheets("Details").Protect , AllowFiltering:=True, AllowFormattingCells:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True

' 根据错误状态显示提示
If hasError Then
    MsgBox "部分连接刷新失败：" & vbCrLf & vbCrLf & errorMsg, vbExclamation, "刷新异常"
Else
    MsgBox "Refresh Complete", vbInformation, "刷新完成"
End If
End Sub

关键修改说明： #

• 新增hasError布尔变量跟踪是否有错误发生，errorMsg字符串记录所有错误详情
• 将On Error Resume Next缩小到单个连接的刷新操作，捕获后立即记录错误并清除状态，避免影响后续逻辑
• 最后根据hasError的值判断是显示成功提示还是错误汇总提示
• 优化了原代码的嵌套If判断为ElseIf，让逻辑更简洁

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