Excel VBA 逆引き集 | PowerQueryとの連動

外部連携 × Power Queryとの連動

Excelには Power Query という強力なデータ取得・変換機能があります。
通常はGUI操作で使いますが、VBAから呼び出すことで「外部データの取り込みや更新」を自動化できます。
初心者向けに、コード例やテンプレートをかみ砕いて説明します。

基本の考え方

• Power Queryとは？
  外部データ（CSV、DB、Web APIなど）を取り込み、整形してExcelに展開する機能。
• VBA連動のメリット:
  • ボタン1つで最新データを再取得
  • 複数クエリをまとめて更新
  • 定型処理を自動化

テンプレ1：Power Queryのクエリを更新

Sub Refresh_PowerQuery()
    ' クエリ名を指定して更新
    ThisWorkbook.Queries("顧客マスタ").Refresh
    
    MsgBox "Power Queryの顧客マスタを更新しました！"
End Sub

Sub Refresh_PowerQuery()
    ' クエリ名を指定して更新
    ThisWorkbook.Queries("顧客マスタ").Refresh
    
    MsgBox "Power Queryの顧客マスタを更新しました！"
End Sub

• ポイント:
  • ThisWorkbook.Queries("クエリ名") で対象クエリを指定。
  • クエリ名は「Power Queryエディタ」で確認できる。

テンプレ2：すべてのPower Queryを更新

Sub Refresh_AllPowerQuery()
    Dim q As Query
    For Each q In ThisWorkbook.Queries
        q.Refresh
    Next q
    
    MsgBox "すべてのPower Queryを更新しました！"
End Sub

Sub Refresh_AllPowerQuery()
    Dim q As Query
    For Each q In ThisWorkbook.Queries
        q.Refresh
    Next q
    
    MsgBox "すべてのPower Queryを更新しました！"
End Sub

• ポイント:
  • For Each で全クエリをループ。
  • 複数の外部データを一括更新できる。

テンプレ3：テーブルにロードされたクエリを更新

Sub Refresh_QueryTable()
    Dim ws As Worksheet
    Dim lo As ListObject
    
    Set ws = Worksheets("Data")
    Set lo = ws.ListObjects("顧客マスタ")
    
    lo.QueryTable.Refresh BackgroundQuery:=False
    
    MsgBox "テーブルにロードされたクエリを更新しました！"
End Sub

Sub Refresh_QueryTable()
    Dim ws As Worksheet
    Dim lo As ListObject
    
    Set ws = Worksheets("Data")
    Set lo = ws.ListObjects("顧客マスタ")
    
    lo.QueryTable.Refresh BackgroundQuery:=False
    
    MsgBox "テーブルにロードされたクエリを更新しました！"
End Sub

• ポイント:
  • Power Queryの結果をテーブルにロードした場合は ListObject.QueryTable.Refresh。
  • BackgroundQuery:=False で同期更新。

テンプレ4：ボタンに割り当てて自動更新

Sub Refresh_Button_Click()
    ' 複数クエリをまとめて更新
    ThisWorkbook.Queries("顧客マスタ").Refresh
    ThisWorkbook.Queries("商品マスタ").Refresh
    
    MsgBox "顧客マスタと商品マスタを更新しました！"
End Sub

Sub Refresh_Button_Click()
    ' 複数クエリをまとめて更新
    ThisWorkbook.Queries("顧客マスタ").Refresh
    ThisWorkbook.Queries("商品マスタ").Refresh
    
    MsgBox "顧客マスタと商品マスタを更新しました！"
End Sub

• ポイント:
  • フォームコントロールボタンにこのマクロを割り当てる。
  • ワンクリックで最新データを取得可能。

例題で練習

'例1：顧客マスタクエリを更新
'例2：すべてのクエリを更新
'例3：テーブルにロードされたクエリを更新
'例4：ボタンに割り当てて複数クエリを更新

'例1：顧客マスタクエリを更新
'例2：すべてのクエリを更新
'例3：テーブルにロードされたクエリを更新
'例4：ボタンに割り当てて複数クエリを更新

初心者向けポイント

• Power QueryはGUIで作成 → VBAで更新
• Queriesコレクションでクエリを操作
• ListObject.QueryTable.Refreshでテーブル連動
• ボタンにマクロを割り当てると便利
• Excelを「外部データ同期ツール」として活用できる

👉 この「外部連携 × Power Query連動テンプレ」を覚えておけば、Excel VBAで 外部データをPower Query経由で取り込み、自動更新する処理 を簡単に作成できます。