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

问题:

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?

回答1:

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.

回答2:

I beleive you'd better avoid such methods as they can cause compatibility problems as well as some other.

If you learn M, you probably won't need to edit code with VBA.

回答3:

A bit late to the party, but additionally, for anyone who views this moving forward one can use:

Thisworkbook.Queries("LATEST").Formula = mFormula

Note, ThisWorkbook is preferable to ActiveWorkbook.