[Excel VBA] How to Deploy Power Query Results as a “Table” (ListObject)

2025年12月27日

Introduction

Placing data retrieved via Power Query into a worksheet as a Table (ListObject) makes it much easier to sort and aggregate. This article explains how to use VBA to deploy an existing query as an Excel Table using an external data source connection.

Prerequisites

Item	Details
Excel Version	Microsoft 365 / 2019 / 2016
Preparation	Power Query named “Query_Import” must be registered in the workbook.

Sample Code

Sub LoadQueryAsTable()

    Dim targetTbl As ListObject   ' Stores the generated table
    Dim tblDest   As Range        ' Insertion destination cell
    
    ' Specify the insertion starting cell
    Set tblDest = Sheet1.Range("A1")
    
    ' Add a ListObject and specify Power Query as an external connection
    Set targetTbl = Sheet1.ListObjects.Add( _
        SourceType:=xlSrcExternal, _
        Source:= _
            "OLEDB;" & _
            "Provider=Microsoft.Mashup.OleDb.1;" & _
            "Data Source=$Workbook$;" & _
            "Location=Query_Import;", _
        Destination:=tblDest _
    )
    
    ' Configure the query table and deploy the data
    With targetTbl.QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Query_Import]")
        .Refresh
    End With
    
    ' Change the table style if needed (e.g., Light Blue)
    targetTbl.TableStyle = "TableStyleLight9"
    
    MsgBox "Power Query data has been deployed as a table.", vbInformation

End Sub

Key Points of the Code

Process	Explanation
ListObjects.Add	Set `SourceType:=xlSrcExternal` and specify the internal query using `Location=Query_Import`.
CommandType = xlCmdSql	Issuing a `SELECT *` in SQL format provides flexibility for changing filters or column selections later.
Table Style	You can set any style name, such as `TableStyleLight9`.

Application Ideas

Requirement	Implementation Example
Place table on another sheet	Change the reference of `tblDest` to something like `Sheet2.Range("B3")`.
Variable query names	Use a variable like `Dim qName As String: qName = "Query_Import"` and insert it into the `Location=` string and SQL.
Create a refresh button	Assign this macro to a Form Control button to enable one-click updates.

Summary

Using ListObject + QueryTable allows you to instantly deploy Power Query results in table format.
Switching to SQL expressions allows you to control column filtering and sorting logic directly from the VBA side.
Converting results to a table ensures smooth integration with PivotTables and Power Pivot.

Please utilize this method to automate your daily or weekly report updates.

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