Excel 2016中如何捕获PowerQuery刷新的详细错误信息？

这个问题确实挺头疼的——Excel把PowerQuery的具体错误给包装成了通用的1004错误，根本没法精准定位问题。我之前也踩过这个坑，下面几个方法能帮你拿到更详细的错误详情：

方法一：直接刷新PowerQuery查询对象（而非WorkbookConnection） #

WorkbookConnection.Refresh会把PowerQuery的底层错误封装成模糊的1004，而直接操作Workbook.Queries集合里的查询对象，能绕过这个封装，捕获到和PowerQuery GUI里一致的具体错误。

试试这段VBA代码：

Sub RefreshQueryWithDetailedErrors()
    Dim targetQuery As WorkbookQuery
    ' 替换成你的PowerQuery名称
    Set targetQuery = ThisWorkbook.Queries("你的查询名称")
    
    On Error Resume Next
    ' 直接刷新查询对象
    targetQuery.Refresh
    If Err.Number <> 0 Then
        MsgBox "查询刷新失败：" & vbCrLf & _
               "错误代码：" & Err.Number & vbCrLf & _
               "详细描述：" & Err.Description, vbCritical
    End If
    On Error GoTo 0
End Sub

运行后你就能拿到类似“数据源连接超时”“SQL语法错误”这类具体的错误信息，再也不用对着1004抓瞎了。

方法二：在M语言中嵌入错误捕获，输出详细错误到工作表 #

如果上面的方法还不够细致，可以在PowerQuery的M代码里直接处理错误，把错误的完整信息（比如错误类型、触发原因）输出到表格里，之后VBA可以读取这个表格的内容。

修改你的M查询，添加try...otherwise结构：

let
    // 用try包裹原有数据源步骤
    Source = try Sql.Database("你的服务器名", "你的数据库名") otherwise 
        #table(
            {"错误类型", "错误详情", "错误原因"},
            {
                {"数据源错误", Error.Message(Source), Error.Reason(Source)}
            }
        ),
    // 保留你原有的其他查询步骤（无错误时正常执行）
    // ... 你的原有查询步骤 ...
in
    Source

刷新后如果出错，PowerQuery会返回一个包含错误详情的表格，VBA可以直接读取对应的工作表单元格获取这些信息。

方法三：针对OLEDB连接的专属错误捕获 #

如果你的PowerQuery连接是基于OLEDB的，可以通过OLEDBConnection对象直接访问ADO的错误集合，拿到更底层的数据库错误：

Sub RefreshOLEDBConnectionWithErrors()
    Dim targetConn As WorkbookConnection
    Dim oledbConn As OLEDBConnection
    ' 替换成你的OLEDB连接名称
    Set targetConn = ThisWorkbook.Connections("你的连接名")
    Set oledbConn = targetConn.OLEDBConnection
    
    On Error Resume Next
    oledbConn.Refresh
    If Err.Number <> 0 Then
        Dim adoError As ADODB.Error
        ' 遍历ADO错误集合，获取所有底层错误
        For Each adoError In oledbConn.ADOConnection.Errors
            MsgBox "OLEDB底层错误：" & vbCrLf & _
                   "错误代码：" & adoError.Number & vbCrLf & _
                   "详细描述：" & adoError.Description, vbCritical
        Next adoError
    End If
    On Error GoTo 0
End Sub

这个方法适合需要深入数据库层面排查错误的场景。

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