Excel Power Query – VBA to Edit a Parameter Value

Last modified date

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 Query1 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.

Over 20 year experience at being passionate about training, solving problems and loving a new challenge especially in the Microsoft's Power Platform suite.