Excel VBA从仅连接查询创建ListObject表时触发运行时错误5

错误原因分析 #

你遇到的「运行时错误'5'」是因为两段代码的Source参数都不符合xlSrcModel类型的要求：

• 第一段代码直接引用整个数据模型的总连接ThisWorkbookDataModel，这个连接对应整个Power Pivot模型，不是单个查询的专属连接，无法直接用来创建单个查询的ListObject。
• 第二段代码传入WorkbookQuery对象作为Source，但xlSrcModel要求的Source必须是关联该查询的WorkbookConnection对象，而非Query本身。

可行解决方案 #

方案一：找到查询对应的隐藏数据模型连接 #

当查询加载到数据模型后，Excel会自动创建一个隐藏的专属连接，名称通常为Query - [查询名]（部分版本为Model Connection - [查询名]）。我们可以遍历工作簿连接找到它，再用来创建ListObject：

Dim targetConn As WorkbookConnection
Dim newTable As ListObject

' 遍历所有连接，定位目标查询的数据模型连接
For Each targetConn In ThisWorkbook.Connections
    ' 筛选数据模型类型且名称匹配目标查询的连接
    If targetConn.Type = xlConnectionTypeModel And _
       targetConn.Name Like "Query - DetailedProfit" Then
       
        Set newTable = ActiveSheet.ListObjects.Add( _
            SourceType:=xlSrcModel, _
            Source:=targetConn, _
            Destination:=Range("$A$1") _
        )
        Exit For
    End If
Next targetConn

' 校验是否找到目标连接
If newTable Is Nothing Then
    MsgBox "未找到与DetailedProfit查询对应的数据模型连接"
End If

方案二：直接从Query加载数据到表（绕过数据模型） #

如果不需要依赖数据模型中转，可以直接复用Query的Power Query逻辑，直接创建ListObject并加载数据，这种方法更简单且不易出错：

Dim targetQuery As WorkbookQuery
Dim newTable As ListObject

Set targetQuery = ThisWorkbook.Queries("DetailedProfit")

' 创建空ListObject并绑定Query的数据源
Set newTable = ActiveSheet.ListObjects.Add( _
    SourceType:=xlSrcRange, _
    Source:=Range("A1") ' 仅作为占位，实际由Query填充数据
)
With newTable.QueryTable
    .Connection = targetQuery.Formula
    .CommandType = xlCmdSql
    .Refresh BackgroundQuery:=False ' 同步刷新数据
End With

注意事项 #

• 可以通过【数据>连接>全部连接】查看隐藏的数据模型连接（需勾选「显示所有连接」选项），确认目标连接的准确名称。
• 方案二的优势是不需要依赖数据模型的隐藏连接，直接复用Query的转换逻辑，更适合自动化场景。

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