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

  • 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 underying 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 tabel, 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 quieries?

    Thursday, July 4, 2019 10:46 AM

All replies

  • Hi,

    Since this issue is more related to VBA, I'm moving it to a Development forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=officegeneral 

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thank you for your understanding.

    Regards,

    Bella Wu


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to shareexplore and talk to experts about Microsoft Office 2019. 

    Friday, July 5, 2019 3:21 AM
  • Did you try copying the table and paste it into the new worksheet as values only and then convert it back to an excel table like below?

    Dim SourceWb As Workbook
    Dim SourceSh As Worksheet
    Dim TargetWb As Workbook
    Dim TargetSh As Worksheet
    Dim SourceTbl As ListObject
    
    Set SourceWb = ActiveWorkbook
    Set SourceSh = SourceWb.ActiveSheet
    Set SourceTbl = SourceSh.ListObjects(1)
    
    SourceTbl.Range.Copy
    
    Set TargetWb = Workbooks.Add
    Set TargetSh = TargetWb.Worksheets(1)
    
    With TargetSh.Range("A1")
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
        .PasteSpecial xlPasteColumnWidths
    End With
    
    TargetSh.ListObjects.Add(xlSrcRange, TargetSh.Range("A1").CurrentRegion, , xlYes).Name = _
            "Table1"
    
    Application.DisplayAlerts = False
    TargetWb.SaveAs SourceWb.Path & "\" & "Copy.xlsx"
    TargetWb.Close
    Application.DisplayAlerts = True


    Subodh Tiwari (Neeraj) sktneer

    Saturday, July 6, 2019 6:02 PM