Update: Refresh Power Queries With VBA

Some time back I posted a routine to refresh Power Queries with VBA, allowing you to refresh all Power Queries in a workbook.

Things Changing…

The challenge with that specific macro is that it refers to the connection name, which may not always start with Power Query.  (As we move closer to Excel 2016, we are now aware that these queries will NOT have names starting with Power Query.)

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.  While that works, it’s still not ideal, as names can be volatile.  It makes more sense to try and find a method that is cannot be broken by a simple name change.

Refresh Power Queries With VBA

The macro below is a more targeted approach that checks the source of the query.  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.

Public Sub UpdatePowerQueriesOnly()
Dim lTest As Long, cn As WorkbookConnection
On Error Resume Next
For Each cn In ThisWorkbook.Connections
lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1")
If Err.Number <> 0 Then
Err.Clear
Exit For
End If
If lTest > 0 Then cn.Refresh
Next cn
End Sub

This macro works with Excel 2010, 2013 and 2016, and should survive longer than the original version I posted.

Share:

Facebook
Twitter
LinkedIn

35 thoughts on “Update: Refresh Power Queries With VBA

  1. Hi Ken
    I seem to have noticed a glitch when importing text files
    When you split column by delimiter all is well providing that the text files does not change at all going forward
    I had a need to change my text file to provide a few extra columns expecting that the split column component of my power query would still work. It would just give me extra fields. It didn't
    The only way I could get it to work again is to actually delete it and re perform the split by delimiter step
    Am I missing something here?
    Cheers
    Anthony
    Ps I would like to buy the M book. Can I purchase directly from you?

  2. Hi Ken,

    I have created a report in Excel connected to the DB using Power Query. The problem I'm running into is when I send the file to another user that doesn't have Power Query installed. They can't refresh the data. Is there a solution to that?

    Thanks,
    Sam

  3. Yep. They need to install Power Query. That's pretty much the only solution at this point.

    This issue will go away in Excel 2016, as the Power Query technology is built in. But for Excel 2010 and 2013, each user that wants to refresh the queries will need Power Query installed.

  4. Hi Ken,
    Is it possible to refer to queries by their name? Sometimes I only need to refresh one of the queries and sometimes I need to refresh them in specific order. Thank you in advance! Rita

  5. Absolutely. Easiest way is to record a macro, then go to Excel's Data tab --> Connections. Select your connection, click Refresh, then stop recording. That will give you the code to refresh that connection only.

  6. Hi Ken,

    I tested your macro and it seems to work, but power queries refresh doesn't run in background and the process actually aborts because the system doesn't wait refresh to finish. Please, note that I put the macro in a module inside the same workbook having 15 power queries to refresh, but I activate it from outside.
    Best, Franco

  7. Franco,

    You say they don't refresh in the background. Did you change the default behavior of the refresh in Data --> Connections?

  8. Hi Ken,
    yes, sure. I think something has changed from Excel 2013 to 2016, because even if I worked on it for a short time, I ran a similar routine, based on the names of the queries, that doesn't work anymore.

  9. Hi Ken,
    I'm using your method to:
    1.access a pgsql server and extract data
    2.copy the results to another worksheet
    3.change the sql string and repeat the process.

    It works fine but every time I copy the worksheet an additional connection to the server is made which leads to a lot of connections to the server.
    How can I make sure that the connection is not copied with the data and I only need 1 connection for the whole process?
    So far I'm experimenting with
    cn.OLEDBConnection.MantainConnection = FALSE and deleting the connection in in the new worksheet but its not working.

    Thanks!

  10. Great question, Rob, and honestly, I'm not sure I have an answer for that. I need to do some further checking, but my guess is that by kicking off a refresh using VBA, Power Query will treat each query as a new connection instance by default. (It doesn't know the connection is still there.)

    As Power Query doesn't have a full object model, I don't know if there even is a way to Open a connection, refresh x queries, then close the connection like you can with ADO. To my understanding, it's not really built that way.

  11. Hi Ken,
    I want to make the excel automatically refresh power query and then save the file. However, if I combine your code and the code to save the file, it does not work. The reason might be that it takes some time to refresh so I can not save it immediately. Could you give me some suggestion on this ? Thanks

  12. Hi Yusheng,

    What I would suggest here is to set the query to a background refresh. That should ensure it completes before it moves on. An example:

    Set cn = wb.Connections("Query - Name")
    With cn
    .OLEDBConnection.BackgroundQuery = False
    .Refresh
    .OLEDBConnection.BackgroundQuery = True
    End With

    Hope that helps!

  13. Hi Ken, thanks for all your posts, I learned a lot, appreciate you sharing it. Could you please help me on how to refresh Power Queries in an order?

    I have a 3 power queries
    1) Power Query 1
    2) Power Query 2 which takes another data set and then merges with Power Query 1
    3) Power Query 3 which takes another data set and then merges with Power Query 2. Power Query 3 results is used in a power pivot report for the end user consumption

    In other words, I want to refresh Power Query in this sequence
    1) Power Query 1
    2) Power Query 2
    3) Power Query 3

    How do I sequence the refresh in Excel 2013? Could you please help?

    Thanks, Anil

  14. The best way is to go to Data --> Connections and rename the queries so that they appear in order. Maybe 1-Query one, 2-Query two, 3-Another query. At this point you can just use a Data -> Refresh All and they will fire in the correct order.

  15. Hi Ken,

    I have diffirent queries with some parameters that i get from a sheet (sheet1) in my workBook. The others sheets (sheet2,sheet3,sheet4) contains the results of my diffirent requets.

    Is there some way to refresh all sheets once my parameters introduced in my sheet1 ?

    Thank you in advance

  16. Not sure I totally follow here. If the this is a chain of queries, it should work by just doing a Data --> Refresh All.

    If you are running Query 1, then extracting results using formulas in a new table from the Query 1 results into a parameter table then using the parameter table to drive more queries... that's a bit tougher. Run a macro to specifically update the first one, then call a macro to refresh each of the others. (If you're working with Power Pivot I would refresh the first, then just record a macro to refresh the first query then perform a Refresh All operation after. Even though you refresh the first query twice, it will be more efficient.)

  17. Ken, Great thanks!
    To get it to work for me I had to remove the Exit For statement in the test as to whether Err.Number was zero otherwise it didn't refresh anything.
    I have 11 queries in this workbook some of which are Connection Only, so I don't know whether that's the reason

  18. Hi Ken,
    I've been having great success with Power Query, particularly having Pivot Tables use the Power Query table as their source.
    What I notice is that upon opening the spreadsheet you have to Refresh All twice for the pivot tables to get the updated data.
    (I am assuming that the first refresh is updating the table contents, then a second refresh is needed for the pivot tables).
    Do you know of a way to automate this "double Refresh"?

    James

  19. Only via using VBA. Easiest way is to set up a button and record the following two actions:
    -Do a Refresh All
    -Right click one of your Pivots and choose Refresh

    That should take care of it. It does seem crazy that the Pivot seems to update before Power Query. It's been brought up with Microsoft, but still no fix as of yet.

  20. I have Excel 2016 file that updates 25 Workbook queries from an external file. When I Refresh ALL. I get the following error: "Attempted to read or write protected memory. This is often an indication that other memory is corrupt."

    One solution is to update the queries one by one. I have used your updated solution in the past but it has stopped working (VBA does not trigger the sequential refresh).

    Can you please give updated code that is Excel 2016 proof?

    Thanks

  21. Hey Jan,

    If you can do them individually, then I'm almost 100% certain that this is not a version issue, but rather a memory issue. Are you by any chance running a 32 bit version of Excel 2016? Going to 64 bit may help.

  22. Do you know if the August 2016 update of power Query started naming the queries as Query - NAME instead of Power Query - NAME. I have had instances of going to a power query that was working, making a change and then close and reload to save the change, then any table or pivot table that uses the connection not refreshing (also not able to get the connection properties for tables or the Pivot Table tab for pivot tables). When I look at the workbook connections it looks like the name has changed so the tables and pivot tables cannot find the connection.

  23. Hi Ken,

    Can we update this code when the sheets are protected?

    Thanks,
    Mielkew

  24. Thanks so much! I would surely have not been able to figure this out without this page. I was experiencing the common problem of the pivot tables refreshing before the power query finished. Using the code from this page and the
    code snippet from the comments it is now working perfectly from one button!
    Thanks again!

  25. Hey Ken,

    I came across this topic when playing around with PQ and VBA... What I'm trying to do, is get the data from the query into vba, without materialising it in a ListObject. I've tried using the classic adodb recordset, but that doesn't seem to work. Any ideas?

  26. Hey Wouter,

    I'm not aware of any way to do this, no. Power Query doesn't manifest the data until it's going to load it somewhere, either a listobject or the data model. And unfortunately we can't use VBA to read from the data model either. 🙁

  27. Ken,

    I know you wrote this post over two years ago, but it's still incredibly relevant.

    Just used it in a quick PQ project.

    Thank you for sharing your knowledge.

    Doug

  28. hi Ken,

    Thanks, for this post, it has helped with a issue I have had, I was not able to get your version for Oct 2014 to work lTest was always 0, the updated version now works, so very happy. I have two test connections but when I run the macro lTest is updated to value 7
    I am assuming this is to do with the line
    lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1")
    but I do not have enough VBA knowledge to understand this line of code.

    the additional comments you have added to other peoples questions have also helped.

    Thanks

  29. Thank you so much, I have struggled with finiding the answer on this, turns out refreshing Power Queries is different to standard queries, who knew! Lol.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts