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.
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.
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]"
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>
Another late response but it might help someone somewhere! I find it easiest to define both your old source and new source as strings then use the replace function to do a switch. This means you don't need to enter any filepaths in excel itself as long as you've nailed the variable paths further up the code :)
Look at entering some variation of the following code:
Dim pqTable As WorkbookQuery 'replace pqTable with any name
Dim oldSource As String
Dim newSource As String
Set pqTable = ThisWorkbook.Queries("Your Query Here")
oldSource = Split(pqTable.Formula, """")(1)
newSource = "Your file path here"
pqTable.Formula = Replace(pqTable.Formula, oldSource, newSource)