使用VBA在Excel中创建数据模型时遇‘查询不存在’错误

一、核心问题：连接创建的字符串拼接错误 #

你当前Add2方法的参数里存在字符串转义混乱的问题，这是触发“查询不存在”报错的主要原因。原代码里误用了HTML转义符"和&，导致连接参数无法正确识别查询名称。

修复后的连接创建代码 #

ThisWorkbook.Connections.Add2 _
    Name:="Query - " & myQuery.Name, _
    Description:="Connection to the '" & myQuery.Name & "' query in the workbook.", _
    ConnectionString:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & myQuery.Name, _
    CommandText:="[" & myQuery.Name & "]", _
    CommandType:=6, _
    AddToModel:=True, _
    MaintainConnection:=False

关键修正点：

• 移除所有HTML转义符，用标准VBA字符串拼接方式
• 命令文本用[查询名]格式包裹，符合Mashup连接的语法要求

二、关于RefreshQueries找不到查询的问题 #

你写的RefreshQueries遍历的是工作表上的QueryTable和ListObject，但你创建的WorkbookQuery是工作簿级别的后台查询，并没有加载到任何工作表上，所以这些对象自然不存在。

正确的工作簿查询刷新代码 #

如果要刷新工作簿级别的查询，直接遍历Queries集合即可：

Public Sub RefreshWorkbookQueries()
    Dim qry As WorkbookQuery
    For Each qry In ActiveWorkbook.Queries
        On Error Resume Next
        ThisWorkbook.Connections("Query - " & qry.Name).Refresh
        On Error GoTo 0
    Next qry
    Set qry = Nothing
End Sub

或者在创建连接后直接刷新当前连接：

Dim newConn As WorkbookConnection
Set newConn = ThisWorkbook.Connections.Add2(...)
newConn.Refresh

三、额外调试与兼容性优化 #

1. 添加延迟执行：部分Excel版本需要一点时间完成查询注册，创建查询后加一行DoEvents：

Set myQuery = ActiveWorkbook.Queries.Add(...)
DoEvents ' 让Excel完成查询后台注册

2. 验证查询名称：创建查询后立即打印名称，确认没有空格或特殊字符：

Debug.Print "Created query: " & myQuery.Name

四、完整修复后的核心代码片段 #

Dim myServer As String
Dim myDatabase As String
Dim myTable As String
Dim mySchema As String
Dim myQueryName As String
Dim myQuery As WorkbookQuery
Dim x As Integer
Dim newConn As WorkbookConnection

For x = 0 To Me.lstSupplementalTables.ListCount - 1
     If Me.lstSupplementalTables.Selected(x) Then
          mySchema = Me.lstSupplementalTables.List(x, 0)
          myTable = Me.lstSupplementalTables.List(x, 1)
          myQueryName = mySchema & "_" & myTable
                   
          ' 创建WorkbookQuery
          Set myQuery = ActiveWorkbook.Queries.Add( _
              Name:=myQueryName, _
              Formula:="let" & vbCrLf & _
                       "    Source = Sql.Database(""" & myServer & """, """ & myDatabase & """)," & vbCrLf & _
                       "    " & myQueryName & " = Source{[Schema=""" & mySchema & """,Item=""" & myTable & """]}[Data]" & vbCrLf & _
                       "in" & vbCrLf & _
                       "    " & myQueryName)
          
          DoEvents ' 等待Excel完成查询注册
          
          ' 创建数据模型连接
          Set newConn = ThisWorkbook.Connections.Add2( _
              Name:="Query - " & myQuery.Name, _
              Description:="Connection to the '" & myQuery.Name & "' query in the workbook.", _
              ConnectionString:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & myQuery.Name, _
              CommandText:="[" & myQuery.Name & "]", _
              CommandType:=6, _
              AddToModel:=True, _
              MaintainConnection:=False)
          
          ' 刷新连接（可选）
          newConn.Refresh
     End If
Next x

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