如何从Power Query创建带数据连接的Excel表格？

问题场景 #

• 用Power Query生成Excel表格，但表格未关联对应的数据连接
• 刷新数据时，Power Query编辑器内数据正常更新，但Excel表格无同步变化
• 尝试运行VBA代码LoopToCreateTest，未能成功建立连接
• 手动将查询转为「仅连接」后重新加载表格，可正常刷新同步数据
• 修改源数据后重新创建表格，能获取到最新数据变更

可能原因 #

1. 创建表格时未正确绑定Power Query的连接对象，导致表格只是静态数据副本，而非动态连接的结果
2. 自定义的LoopToCreateTest代码可能未正确调用Power Query的连接创建逻辑，比如漏设刷新属性、未关联到目标表格

解决方案 #

方案1：手动重新绑定连接（快速解决）

• 打开「数据」选项卡 → 「现有连接」，找到对应的Power Query连接
• 选中连接后点击「打开」，在「导入数据」对话框选择「表格」，指定放置位置，确认后生成的表格会自动关联连接，刷新即可同步

方案2：修复VBA代码，自动创建带连接的表格

如果需要自动化操作，用以下代码替代原LoopToCreateTest，确保创建连接并绑定表格：

Sub CreateTableWithPQConnection()
    Dim conn As WorkbookConnection
    Dim pqQuery As WorkbookQuery
    Dim destRange As Range
    Dim tbl As ListObject
    
    ' 替换为表格放置的目标单元格
    Set destRange = ThisWorkbook.Sheets("Sheet1").Range("A1")
    ' 替换为你的Power Query查询名称
    Set pqQuery = ThisWorkbook.Queries("你的PQ查询名")
    
    ' 检查连接是否已存在，不存在则新建
    On Error Resume Next
    Set conn = ThisWorkbook.Connections(pqQuery.Name)
    On Error GoTo 0
    
    If conn Is Nothing Then
        Set conn = ThisWorkbook.Connections.Add2( _
            Name:=pqQuery.Name, _
            Description:="Power Query连接: " & pqQuery.Name, _
            ConnectionString:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & pqQuery.Name & ";Extended Properties=""""", _
            CommandText:="SELECT * FROM [" & pqQuery.Name & "]", _
            lCmdtype:=xlCmdSql, _
            CreateModelConnection:=True, _
            ImportRelationships:=True _
        )
    End If
    
    ' 创建关联连接的表格
    Set tbl = destRange.Worksheet.ListObjects.Add( _
        SourceType:=xlSrcQuery, _
        Source:=conn, _
        Destination:=destRange _
    )
    tbl.Name = "PQ关联表格"
    ' 可选：设置文件打开时自动刷新
    conn.RefreshOnFileOpen = True
End Sub

• 将代码中的你的PQ查询名和表格位置替换为实际信息
• 运行代码后生成的表格会直接绑定Power Query连接，刷新即可同步数据

方案3：调整Power Query的加载设置

• 打开Power Query编辑器，选中目标查询
• 点击「主页」选项卡 → 「关闭并上载至」
• 选择「仅创建连接」，同时勾选「加载到工作表」并指定位置，确认后生成的表格会自动绑定连接，避免生成静态数据

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