[Excel VBA] Procedure for Bulk Deployment of Power Query Results to a Sheet

2025年12月27日

Directly deploying data obtained via Power Query to a worksheet using VBA can significantly simplify operational workflows. This article demonstrates how to use the QueryTable object to load an internal workbook query, paste the results, and then remove the connection once it is no longer needed.

Operation Environment

Item	Details
Excel Version	Microsoft 365 / 2019 / 2016
Existing Query Name	Query_Sales (Already registered in Power Query)

Sample Code

Sub DeployPowerQueryResult()

    Dim qtObj  As QueryTable  ' QueryTable for deployment
    Dim dstRng As Range       ' Starting cell for pasting
    
    ' Specify the destination cell
    Set dstRng = Sheet1.Range("A1")
    
    ' Add QueryTable and set Power Query as the data source
    Set qtObj = Sheet1.QueryTables.Add( _
        Connection:="OLEDB;" & _
                   "Provider=Microsoft.Mashup.OleDb.1;" & _
                   "Data Source=$Workbook$;" & _
                   "Location=Query_Sales;", _
        Destination:=dstRng, _
        Sql:="SELECT * FROM [Query_Sales]" _
    )
    
    ' Retrieve data
    qtObj.Refresh
    
    ' Delete the connection object if it is no longer needed
    qtObj.Delete
    
    MsgBox "Power Query results have been deployed.", vbInformation

End Sub

Key Points of the Code

Feature	Details
Connection String	Combining `Provider=Microsoft.Mashup.OleDb.1` and `Data Source=$Workbook$` allows an internal query to be recognized as an external data source.
Location Parameter	Specifies the name of the Power Query (in this case, `Query_Sales`).
SQL Expression	`SELECT * FROM [Query_Sales]` is the basic form for retrieving all columns. You can filter data on the server side by adding column names or conditions as needed.
qtObj.Delete	Deleting the connection after data retrieval leaves only the data on the sheet, which helps prevent file size inflation.

Application Ideas

Objective	Implementation Method
Paste as a Table	Use `ListObjects.Add` and specify the same connection string as the source.
Dynamic Sheet Switching	Turn `Worksheets("TargetSheet")` into a variable to deploy the same query to multiple sheets.
Selecting Columns or Sorting	Edit the `Sql` argument to something like `SELECT Column1, Column2 FROM [Query_Sales] ORDER BY Column1`.

Summary

By using QueryTable, Power Query results can be instantly deployed to a worksheet.
Deleting the connection after deployment keeps only the data, thereby suppressing the file size.
Modifying the SQL statement to narrow down columns or rows can reduce loading times.

Consider integrating the link between Power Query and VBA to automate daily or weekly report updates.

よかったらシェアしてね！