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.
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>