How to change source of a power query using VBA?

Question

So in Excel 2016, they have this neat tool called Power Query, basically a glorified excel table. Every table has steps in it for filtering, removing columns, etc... The first step is the source step, to assign a connection string basically to retrieve data, normally this source just points back to the query which created it.

Anyways, I'm trying in VBA to dynamically change the source of these power queries, anyone have any ideas?

I tried using the whole connections vibe, but was unsuccessful.

Answer 1

You can access the query through ActiveWorkbook.Item. You can then modify the Formula property. You can find the documentation on these objects here.

Please note that the Power Query object model was only added to VBA in Excel 2016 and cannot be accessed in prior versions.