How to change source of a power query using VBA?

excel vba powerquery
James Heffer picture James Heffer · Mar 18, 2016 · Viewed 13k times · Source

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

Alejandro Lopez-Lago - MSFT picture Alejandro Lopez-Lago - MSFT · Mar 18, 2016

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.

Related questions

How to automate a power query in VBA?

I have data in sheet 1. Normally I go to power query and do my transformations, then close, and load to an existing sheet 2. I would like to automate this using VBA, where I can just run my power query automatically …

Read More
Auto-updating Power Query Connection via VBA

I have a Power Query set in myexcel.xlsx. I set its connections's properties as this and this. I wrote a VBA code like the following Sub UpdateData() Dim filename As String Dim wbResults As Workbook filename = "C:\myexcel.xlsx" …

Read More
Delete all queries in a workbook

I am trying to write a sub to delete all queries in a workbook. I have got the code below: Dim CN As Variant Dim qTable As QueryTable For Each CN In ThisWorkbook.Connections CN.Delete Next CN For Each …

Read More