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 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 to Edit a Parameter Value
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 SubDescription
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.