Excel Power Query – VBA to Edit a Parameter Value

This post is to document how to use Excel VBA to edit a parameter value without using the a cell reference in Power Query. My query was using Web.Contents which works with parameters in Excel Power Query¹ but doesn’t like a function as part of the path.

I started with Chris Webb’s great post on using an Excel named range and Web.Contents gave me problems. I would try Chris Webb’s method first which is found here https://blog.crossjoin.co.uk/2014/07/22/working-with-excel-named-ranges-in-power-query/

So I want a button to take the value from a cell and update the value in a parameter. I searched the web and found one solution buried in a MrExcel forum so I’m documenting the solution here mostly so I can find it again later.

VBA Code

Sub ChangeParameterValue(ParameterName As String, ParameterValue As String)

    Dim qry As WorkbookQuery
    Dim formula As Variant
    
    '=== Get the query
    Set qry = ThisWorkbook.Queries(ParameterName)
    
    '=== Split the formula into 3 parts and update the second one
    formula = Split(qry.formula, Chr(34), 3)
    formula(1) = ParameterValue
    
    '=== Update the parameter value
    qry.formula = Join(formula, Chr(34))
    
End Sub

Description

The formula property of a query is really weird and hard to construct so the above function splits on Chr(34), which is a “. It then updates the middle value to the new value and then sticks the three values back together again.

The original forum post I got the solution from can be found at https://www.mrexcel.com/board/threads/vba-code-to-edit-power-query-data-source-settings.1146964/

Footnotes

1 – Web.Contents varies in different versions of Power Query so test carefully and do not assume you can copy and paste queries.