none
Excel 2016 power query editor VBA to copy entire table RRS feed

  • Question

  • I have very large data file.  I have desktop Excel 2016.

    I have a created a reference query "test2" based on an existing query called "test":

        ActiveWorkbook.Queries.Add Name:="test2", _
            Formula:= _
            "let   Source = #""test"","  & _
            "    #""Filtered Rows"" = Table.SelectRows(Source, each ([Ship_From_State] = ""TN"") and ([State] = ""TN""))," &  _
            "    #""Grouped Rows"" = Table.Group(#""Filtered Rows"", {""State""}, {{""Total_Tax_G"", each List.Sum([Total_Tax]), type number}, {""Gross_G"", each List.Sum([Gross]), type number}})" & "in   #""Grouped Rows"""

    I want to put the results in a sheet.  If I "Close and load" to sheet, or "Close and load To" as a connection, either way takes 30 seconds which is too long.  Therefore I open query editor and manually "Copy Entire Table", then close the editor, and paste into a sheet.   But I am unable to record any VBA code because the recorder cannot record the copy action.  What is the VBA code to copy entire table from query editor and paste into sheet?

    Wednesday, March 21, 2018 6:54 PM

Answers

  • Very sorry, but there is no VBA code that will copy from the query editor preview.

    You should be aware that the preview, by design, doesn't contain all the rows if there are a lot of rows and/or a lot of columns.  You may not be getting all the data when you copy from the preview.  You should look at the row count to be sure you are getting all the data you need.  It may be that "Close and Load To" is taking longer because it is loading the complete set.  Consider reducing the columns and rows to only what you need to speed up the load time.

    -jeff

    Sunday, April 1, 2018 4:43 AM

All replies

  • kkaax,

    There is no Power Query object exposed through the Excel object model in VBA. Therefore, you cannot record or manually code any actions performed in Power Query. You can record the actions to create a Power Query query, but these actions are performed in the Excel UI. The creation of a Power Query query in VBA is supported by the WorkbookQuery object (Excel 2016+ only).

    Wednesday, March 21, 2018 8:50 PM
  • Thanks I have Excel 2016 desktop version and my question is

    What is the VBA code to copy entire table from query editor and paste into sheet?  Does it exist.

    If this automation is not possible then I am stuck having to wait 20 minutes to process a file because I have 10 reference queries to "close and load" data into sheets .  "Close and Load" is very inefficient and takes a lot of time.   The manual "copy entire table"/paste in sheet  is fast but I need to have the VBA code to automate it.  


    • Edited by kkaax Thursday, March 22, 2018 12:48 PM
    Thursday, March 22, 2018 12:42 PM
  • "What is the VBA code to copy entire table from query editor and paste into sheet?  Does it exist."

    There is no VBA code to accomplish what you want.

    Thursday, March 22, 2018 2:13 PM
  • Very sorry, but there is no VBA code that will copy from the query editor preview.

    You should be aware that the preview, by design, doesn't contain all the rows if there are a lot of rows and/or a lot of columns.  You may not be getting all the data when you copy from the preview.  You should look at the row count to be sure you are getting all the data you need.  It may be that "Close and Load To" is taking longer because it is loading the complete set.  Consider reducing the columns and rows to only what you need to speed up the load time.

    -jeff

    Sunday, April 1, 2018 4:43 AM