How to Change Excel Power Query Paramaters with VBA

0
votes

How to change Excel PowerQuery Paramaters with VBA?

i.e. These Paramaters:

Excel Power Query

Right now the Paramater has a value of 8, and is used in refreshing all other power queries. How can I change this paramater with VBA?

The Macro recorder does not show anything.

1
excelvbapowerquery
Do you frequently need to change the parameter? If so, you could pick up the value from a cell. Otherwise, just change the value in the M code. – QHarr
Yes, I need to change it frequently. The reason the vba code simply copies the workbook an arbitrary number of times (changing the parameter in each one). The copied workbook is not a macro workbook, which is why I am using Query Parameters. – Jeremy Morren
google.co.uk/… I partic like the ExcelIsFun versions. – QHarr

1 Answers

2
votes

Thanks to QHarr, I figured it out. Simply edit the Power Query M Code - the Parameter value is first item in the Code.

e.g. 

5 meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=true]

5 is the Parameter Value.

To edit, use the "Formula" Property e.g.

ThisWorkbook.Queries([ParameterName]).Formula = 'New code here