☰

Power Query：将查询重新绑定到已删除并重新创建的工作表/表

tag-icon • tag-icon microsoft-excel vba power-query
Power Query：将查询重新绑定到已删除并重新创建的工作表/表

我正在 Microsoft 365 的 Excel 中使用 Power Query。我使用 VBA 而不是图形界面来执行 Power Query 操作。

假设我有一个现有的工作簿，该工作簿包含两个表Source_t和Sink_t（分别在名为Source_w和的工作表中Sink_w），并且我有一个名为的查询Sink_q，该查询从中获取数据Source_t，对其进行一些转换，然后将结果加载到中Sink_t。

请考虑以下 VBA 代码：

Sub BindQueryToWorksheet()
    DeleteWorksheetIfExists "Sink_w"
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Sink_w"
    ActiveWorkbook.RefreshAll
End Sub

Private Sub DeleteWorksheetIfExists(WorksheetName As String)
    Application.DisplayAlerts = False
    On Error Resume Next
    Worksheets(WorksheetName).Delete
    Application.DisplayAlerts = True
End Sub

当 VBA 代码删除时Sink_w（并且，这样做Sink_t），Sink_q将变成“仅连接”查询。当 VBA 代码重新创建Sink_w并执行全部刷新时，Sink_t不会重新创建（并且显然，查询的输出不会加载，因为没有表可以将其加载到其中）。

但Sink_w确实重新创建了，然后我可以使用 Power Query 图形界面的“加载到...”选项创建一个表Sink_w并将数据加载到该表中。（但请注意，创建的表的名称 ---Sink_q与查询的名称相同 --- 不是Sink_t所需的。如果我手动将表重命名为Sink_t，则当我执行全部刷新时，一切仍会按预期工作。）

我的问题

在 VBA 中，我该如何：

  1. 绑定Sink_q回来Sink_t以便刷新全部将创建sink_t（在工作表中sink_w）并填充该表？
  2. 控制创建的表的名称Sink_t，使得它不是Sink_q？

答案1

调用Wrapper，如下所定义，将删除并重新创建工作表，然后将查询重新绑定到它作为数据源。

Sub Wrapper()
    BindQueryToWorksheet DestinationWorksheetName:="Sink_w", _
                         DestinationTableName:="Sink_t", _
                         SourceQueryName:="Sink_q"
End Sub

Private Sub BindQueryToWorksheet( _
                                    DestinationWorksheetName As String, _
                                    DestinationTableName As String, _
                                    SourceQueryName As String _
                                )

    DeleteWorksheetIfExists DestinationWorksheetName
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = DestinationWorksheetName
    Range("A1").Select

    With ActiveSheet.ListObjects.Add( _
                     SourceType:=0, _
                     Source:="OLEDB;" & _
                             "Provider=Microsoft.Mashup.OleDb.1;" & _
                             "Data Source=$Workbook$;" & _
                             "Location=" & SourceQueryName & ";" & _
                             "Extended Properties=""""", _
                     Destination:=Range("$A$1") _
                                    ).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & SourceQueryName & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = DestinationTableName
        .Refresh BackgroundQuery:=False
    End With
End Sub

Private Sub DeleteWorksheetIfExists(WorksheetName As String)
    Application.DisplayAlerts = False

    On Error Resume Next
    Worksheets(WorksheetName).Delete

    Application.DisplayAlerts = True
End Sub

相关内容