VBA から Power Query にパラメータを渡して動的更新する方法:初心者向けの簡単サンプル付き

目次

概要

Excel で商品別レポートなどを作成する際、抽出対象の商品名をセルや VBA から変更し、そのまま Power Query を再実行したい場面がございます。本記事では、VBA でクエリのパラメータを差し替えて即座に更新する手順を、コード付きで解説いたします。


使用するコード

1. Power Query 側(M 言語)

以下の M コードは、テーブル Orders を読み込み、クエリ ItemParam に格納された値でフィルタリングする例です。

// クエリ名: OrdersFiltered
let
    // 別クエリ ItemParam からパラメータ取得
    targetItem = ItemParam,

    // ワークブック内テーブル Orders を読み込み
    Source      = Excel.CurrentWorkbook(){[Name = "Orders"]}[Content],

    // パラメータで行を抽出
    Filtered    = Table.SelectRows(Source, each [Product] = targetItem)
in
    Filtered

補足

  • ItemParam はスカラー値を返すクエリとして作成し、初期値のみ設定しておきます。
  • Orders はワークブック内の構造化テーブル(列名 Product など)です。

2. VBA 側

' モジュール: ModuleUpdate
Sub UpdatePowerQueryParameter()
    Dim newValue As String
    newValue = "Orange"  ' 動的に渡す商品名

    ' パラメータ用クエリ ItemParam の式を書き換え
    ThisWorkbook.Queries("ItemParam").Formula = "="" " & newValue & " """

    ' 抽出結果を含むテーブルを同期更新
    Worksheets("Report").ListObjects("tblExtract") _
            .QueryTable.Refresh BackgroundQuery:=False
End Sub

コードのポイント

項目説明
ItemParamスカラー値のみを返すクエリとして作成し、VBA から式を書き換えます。
ThisWorkbook.Queries("ItemParam").Formulaクエリ式を直接変更するため、参照設定は不要です。
QueryTable.RefreshBackgroundQuery:=False を指定すると完了を待って次の処理へ進めます。

注意点

  1. クエリ名の厳密さ
    ItemParamOrdersFiltered の名称を変更した場合、VBA 側のコードも必ず更新してください。
  2. ダブルクォートの扱い
    パラメータを書き換える際は、"" で囲む文字列リテラルの形式を崩さないようご注意ください。
  3. 複数パラメータ
    複数の条件を動的に変更したい場合は、パラメータ用クエリを必要数用意し、それぞれの .Formula を書き換える方式が保守しやすくなります。

応用例

  • 日付範囲の動的抽出
    開始日・終了日をそれぞれパラメータクエリ化し、Table.SelectRows で範囲抽出します。
  • 複数シートへの自動出力
    商品名ごとにループ処理を行い、出力先シートを切り替えながら Refresh すると、複数レポートを一括生成できます。

まとめ

VBA から Power Query のパラメータを制御することで、ユーザー入力やシステムイベントに応じたレポートを自動生成できます。スカラー値を返すクエリを活用し、式を動的に書き換える方法は、セルを増やさず UI を簡潔に保てる点が利点です。ぜひ本サンプルコードを基に、業務シナリオへ応用していただければ幸いです。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

私が勉強したこと、実践したこと、してることを書いているブログです。
主に資産運用について書いていたのですが、
最近はプログラミングに興味があるので、今はそればっかりです。

目次