How to edit the source of a power query using VBA?

excel, powerquery, vba

Solution

You can use `ActiveWorkbook.Queries.Item` to get the query you want and use the `Formula` property to update the query's formula, like so:

`ActiveWorkbook.Queries.Item("LATEST").Formula = "let MyNewFormula = 1 + 1 in Source"`

Note: this only works on Excel 2016 or later.

Problem

I am trying to edit the source of one of my queries using VBA. This is what I have so far: ``` Dim mFormula As String mFormula = _ "let Source = Excel.Workbook(File.Contents(wbname), null, true) in Source" query1 = ActiveWorkbook.Queries.Add("LATEST", mFormula) ``` I set `wbname` previously in my code. "LATEST" is already added, instead of delete it and read it, I would just like to change the source. Is this possible?

Original source