How to save a Power Query table as a regular table (without connections) in a new workbook?

Question

I'm looking for a simple solution to exporting a single worksheet containing one loaded Power Query table to a new workbook without the underlying connections and queries.

I think Microsoft changed something in Excel 2019 regarding the behaviour of Power Query tables (ListObject) and the way their connections are being saved between copies. Previously (Excel 2016) when you created a copy either of the table or the worksheet containing the table, only the values and formatting would be preserved and now Excel also duplicates all the queries and connections that are needed to refresh that table. I don't want that - I don't want to expose the Power Query code to anyone I'm sending the data to.

Previously I could use this simple code:

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

and now I need to take some extra steps:

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

I know it's not that much more code, but I feel that I may be missing something and maybe the solution can be a little simpler. Is anyone aware of the exact changes made by Microsoft that lead to this behaviour? Were there any changes made in the VBA model that would help with copying/pasting Power Query Tables without preserving the underlying queries?

I'd lose not only cell formatting (background color, font, etc.) but also number formatting. Lets say I have EAN codes in one column. Some of them start with a "0" and when i copy/paste only values, those text values are being transformed into number which isn't what I want. I tried a two-step process: 1) paste values (xlPasteValues), 2) paste formatting (xlPasteFormats), but the second step also copies connections and queries over. — Dariusz Kuśnierek

Gangula Gangula · Accepted Answer · 2019-07-11T14:11:39

Instead of Copying the entire sheet and saving it, I recommend copying the table and pasting it in a new sheet and then save the new sheet.

Pasting the table as values first and then pasting the format alone later. Below is the code:

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

Please edit the Query Table name and the SaveAs method above to include your table name and file name respectively.
I have tested this and it's working.

How to save a Power Query table as a regular table (without connections) in a new workbook?

2 Answers