[VBA] How to Pass Parameters to Power Query for Dynamic Updates | Easy Guide for Beginners

2025年12月29日

Overview

When creating reports by product in Excel, you may encounter situations where you want to change the target product name via a cell or VBA and immediately re-run Power Query.

This article explains the steps to replace query parameters using VBA and update the results instantly, including the necessary code.

Code to Use

1. Power Query Side (M Language)

The following M code is an example that reads the Orders table and filters it using the value stored in the query named ItemParam.

// Query Name: OrdersFiltered
let
    // Get parameter from a separate query "ItemParam"
    targetItem = ItemParam,

    // Load the "Orders" table from the current workbook
    Source      = Excel.CurrentWorkbook(){[Name = "Orders"]}[Content],

    // Extract rows using the parameter
    Filtered    = Table.SelectRows(Source, each [Product] = targetItem)
in
    Filtered

Supplement:

ItemParam should be created as a query that returns a scalar value (set an initial value).
Orders is a structured table (with columns such as Product) within the workbook.

2. VBA Side

' Module: ModuleUpdate
Sub UpdatePowerQueryParameter()
    Dim newValue As String
    newValue = "Orange"  ' Product name to pass dynamically

    ' Rewrite the formula for the parameter query "ItemParam"
    ThisWorkbook.Queries("ItemParam").Formula = "="" " & newValue & " """

    ' Synchronously update the table containing the extraction results
    Worksheets("Report").ListObjects("tblExtract") _
            .QueryTable.Refresh BackgroundQuery:=False
End Sub

Key Points of the Code

ItemParam Create this as a query that returns only a scalar value. VBA rewrites its formula.
ThisWorkbook.Queries(“ItemParam”).Formula Since we verify and change the query formula directly, no special reference settings are required.
QueryTable.Refresh BackgroundQuery:=False Specifying this option allows the code to wait for the refresh to complete before proceeding to the next step.

Important Notes

Strict Query Names If you change the names ItemParam or OrdersFiltered, you must update the VBA code as well.
Handling Double Quotes When rewriting parameters, be careful not to break the string literal format enclosed in double quotes ("").
Multiple Parameters If you want to change multiple conditions dynamically, prepare the necessary number of parameter queries and rewrite each .Formula. This method is easier to maintain.

Application Examples

Dynamic Date Range Extraction Turn the Start Date and End Date into parameter queries and filter ranges using Table.SelectRows.
Automatic Output to Multiple Sheets Loop through product names and switch the output destination sheet while refreshing. This allows you to generate multiple reports at once.

Summary

By controlling Power Query parameters from VBA, you can automatically generate reports based on user input or system events.

The method of using queries that return scalar values and dynamically rewriting formulas has the advantage of keeping the UI simple without increasing the number of helper cells. I hope you can apply this sample code to your business scenarios.

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