excel - 如何将 Power Query 表保存为新工作簿中的常规表(无连接)？

Question

我正在寻找一种简单的解决方案，以将包含一个加载的 Power Query 表的单个工作表导出到一个没有底层连接和查询的新工作簿。

我认为 Microsoft 在 Excel 2019 中改变了一些关于 Power Query 表 (ListObject) 的行为以及它们的连接在副本之间保存的方式。以前 (Excel 2016) 当您创建表或包含表的工作表的副本时，只会保留值和格式，现在 Excel 还会复制刷新该表所需的所有查询和连接。我不想这样 - 我不想将 Power Query 代码暴露给任何我要向其发送数据的人。

以前我可以使用这个简单的代码:

Dim SourceWb As Workbook
Dim SourceSh As Worksheet
Dim TargetWb As Workbook
Dim TargetSh As Worksheet

Set SourceWb = ActiveWorkbook
Set SourceSh = SourceWb.ActiveSheet

SourceSh.Copy

Set TargetWb = ActiveWorkbook
Set TargetSh = TargetWb.ActiveSheet

Application.DisplayAlerts = False
TargetWb.SaveAs SourceWb.Path & "\" & "Copy.xlsx"
TargetWb.Close
Application.DisplayAlerts = True

现在我需要采取一些额外的步骤:

Dim SourceWb As Workbook
Dim SourceSh As Worksheet
Dim TargetWb As Workbook
Dim TargetSh As Worksheet
Dim TableCn As WorkbookConnection
Dim TableQr As WorkbookQuery

Set SourceWb = ActiveWorkbook
Set SourceSh = SourceWb.ActiveSheet

SourceSh.Copy

Set TargetWb = ActiveWorkbook
Set TargetSh = TargetWb.ActiveSheet

On Error Resume Next
For Each TableCn In TargetWb.Connections
    TableCn.Delete
Next
For Each TableQr In TargetWb.Queries
    TableQr.Delete
Next

Application.DisplayAlerts = False
TargetWb.SaveAs SourceWb.Path & "\" & "Copy.xlsx"
TargetWb.Close
Application.DisplayAlerts = True

我知道代码没有那么多，但我觉得我可能遗漏了一些东西，也许解决方案可以更简单一些。是否有人知道 Microsoft 所做的导致此行为的确切更改？在不保留基础查询的情况下，是否对 VBA 模型进行了任何有助于复制/粘贴 Power Query 表的更改？

Answer 1

与其复制整个工作表并保存，我建议复制表格并将其粘贴到新工作表中，然后保存新工作表。

先将表格作为值粘贴，然后再单独粘贴格式。下面是代码:

Public Sub Save_Query_Table()
        Range("Query_Table_Name[#All]").Select
        Selection.Copy
        Workbooks.Add
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

        Application.CutCopyMode = False

        ActiveWorkbook.SaveAs Filename:=FullFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

End Sub

请编辑上面的查询表名称和 SaveAs 方法，以分别包含您的表名和文件名。
我已经对此进行了测试并且它正在运行。