目次
概要
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.Refresh | BackgroundQuery:=False を指定すると完了を待って次の処理へ進めます。 |
注意点
- クエリ名の厳密さ
ItemParam
やOrdersFiltered
の名称を変更した場合、VBA 側のコードも必ず更新してください。 - ダブルクォートの扱い
パラメータを書き換える際は、""
で囲む文字列リテラルの形式を崩さないようご注意ください。 - 複数パラメータ
複数の条件を動的に変更したい場合は、パラメータ用クエリを必要数用意し、それぞれの.Formula
を書き換える方式が保守しやすくなります。
応用例
- 日付範囲の動的抽出
開始日・終了日をそれぞれパラメータクエリ化し、Table.SelectRows
で範囲抽出します。 - 複数シートへの自動出力
商品名ごとにループ処理を行い、出力先シートを切り替えながらRefresh
すると、複数レポートを一括生成できます。
まとめ
VBA から Power Query のパラメータを制御することで、ユーザー入力やシステムイベントに応じたレポートを自動生成できます。スカラー値を返すクエリを活用し、式を動的に書き換える方法は、セルを増やさず UI を簡潔に保てる点が利点です。ぜひ本サンプルコードを基に、業務シナリオへ応用していただければ幸いです。