Excel VBA で Power Query の結果をシートに一括展開する手順

2025年7月27日

Power Query で取得したデータを VBA から直接シートへ展開すると、作業手順を大幅に簡略化できます。本記事では QueryTable オブジェクト を使用し、ブック内クエリを読み込んで貼り付けた後、不要になった接続を削除する例を示します。

動作環境

項目	内容
Excel	Microsoft 365／2019／2016
既存クエリ名	`Query_Sales`（Power Query に登録済み）

サンプルコード

Sub DeployPowerQueryResult()

    Dim qtObj  As QueryTable  ' 展開用 QueryTable
    Dim dstRng As Range       ' 貼り付け開始セル
    
    ' 展開先セルを指定
    Set dstRng = Sheet1.Range("A1")
    
    ' QueryTable を追加し、Power Query をデータソースとする
    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]" _
    )
    
    ' データを取得
    qtObj.Refresh
    
    ' 接続オブジェクトが不要であれば削除
    qtObj.Delete
    
    MsgBox "Power Query の結果を展開しました。", vbInformation

End Sub

コードの構成要点

処理	詳細
接続文字列	`Provider=Microsoft.Mashup.OleDb.1` と `Data Source=$Workbook$` を組み合わせると、ブック内クエリを外部データソースとして認識できます。
`Location` パラメータ	Power Query の名前 (`Query_Sales`) を指定します。
SQL 式	`SELECT * FROM [Query_Sales]` は全列を取得する基本形です。必要に応じて列名や条件句を追加し、サーバー側で絞り込み可能です。
`qtObj.Delete`	取得後に接続を削除すると、シート上のデータのみが残り、ブックのサイズ増大を抑えられます。

応用アイデア

目的	実装方法
テーブル形式で貼り付けたい	`ListObjects.Add` を利用し、取得元を同一接続文字列で指定します。
シート名を動的に切り替えたい	`Worksheets("TargetSheet")` を変数化し、複数シートへ同一クエリを展開します。
列選択や並べ替えを行いたい	`Sql` 引数を `SELECT 列1, 列2 FROM [Query_Sales] ORDER BY 列1` のように編集します。