This entry was posted in Excel, Excel Add-ins, General, Office 2010, Office 2013, Office 2016, Power Query, PowerBI by Ken Puls. This macro works with Excel 2010, 20, and should survive longer than the original version I posted. LTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=.1") If it is a connection that is built against Power Query engine (Microsoft.Mashup), then it will refresh the connection, otherwise it will ignore it.ĭim lTest As Long, cn As WorkbookConnection The macro below is a more targeted approach that checks the source of the query. It makes more sense to try and find a method that is cannot be broken by a simple name change. You can also upload your powerbi file to Service and use Analyze in Excel feature to browse the cube but data modifications are not allowed in that case. You can export the data from powerbi visuals and can create a new data model in powerpivot Excel or adhoc analysis. While that works, it’s still not ideal, as names can be volatile. You can not import the PowerBI data into the Excel.
#Can i do web queries in excel 2016 code#
I have seen an approach where someone modified the code I provided earlier to include a variable, allowing you to easily change the prefixed text from Power Query to something else.
(As we move closer to Excel 2016, we are now aware that these queries will NOT have names starting with Power Query.)
The challenge with that specific macro is that it refers to the connection name, which may not always start with Power Query. Some time back I posted a routine to refresh Power Queries with VBA, allowing you to refresh all Power Queries in a workbook.
0 kommentar(er)
