How to change source of a power query using VBA?

5
votes

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.

3
excelvbapowerquery
could you paste in your vba pls? – Marcus D
So in order to do this dynamically, you have, one a sheet, in a cell, your query name. Then in another cell, you have the query formula, and if you want, you can have a description for a query in another cell. Then in VBA, you declare string variables to hold these objects. And use the below code to add a query in Excel. Set qry = ThisWorkbook.Queries.Add(queryName, queryFormula, queryDescription) – James Heffer
Oh, and I figured out how to create, delete, update and refresh power queries, as well as adding tables to the data model, it's all very possible and also in vb.net. – James Heffer

3 Answers

2
votes

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.

1
votes

The queries can be accessed via the Queries collection in a Workbook object. The relevant property for the source is Formula.

Example code:

ActiveWorkbook.Queries.Item("MyQuery").Formula = "[Insert actual M formula here]"
0
votes

Many years later, but I'm adding a different solution for those, like me, still stuck with Excel 2013. As stated by @Alejandro in his response, Power Query was only added to the object model in Excel 2016.

If you are using an older version of Excel, you can use a cell-based solution similar to the one talked about here for relative source paths: https://techcommunity.microsoft.com/t5/excel/power-query-source-from-relative-paths/m-p/206150

Basically have a cell some where in your workbook that contains your query's source. Name that cell, using standard Excel names. In the example below, I've named the cell SourceFileName, and I'm trying to load an Excel file to PowerQuery. The full name of the source Excel file (including path) is in SourceFileName . You can then access the contents of that cell via Power Query:

<previous M code>
sourceFileName= Excel.CurrentWorkbook(){[Name="SourceFileName"]}[Content]{0}[Column1],
Source = Excel.Workbook(File.Contents(sourceFileName ), null, true),
<rest of M code>