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.

36 thoughts on “Update: Refresh Power Queries With VBA”

Mike Alexander on August 20, 2015 at 10:52 am said:

Nice! Thanks Ken.
Anthony Newell on August 31, 2015 at 2:46 am said:

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?
Sam on September 16, 2015 at 6:34 am said:

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
Ken Puls on September 16, 2015 at 7:40 am said:

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.
Rita on September 22, 2015 at 5:40 am said:

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
Ken Puls on September 22, 2015 at 9:34 am said:

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.
Rita on September 23, 2015 at 12:44 am said:

Works great, thank you!
Franco Fornari on October 8, 2015 at 1:29 pm said:

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
Ken Puls on October 8, 2015 at 1:51 pm said:

Franco,

You say they don't refresh in the background. Did you change the default behavior of the refresh in Data --> Connections?
Franco Fornari on October 8, 2015 at 9:25 pm said:

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.
Rob on October 22, 2015 at 2:23 am said:

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!
Ken Puls on October 22, 2015 at 8:01 am said:

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.
Yusheng on January 12, 2016 at 1:10 pm said:

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
Ken Puls on January 14, 2016 at 4:10 pm said:

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!
Anil Abraham on March 23, 2016 at 3:10 pm said:

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
Ken Puls on March 29, 2016 at 1:26 pm said:

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.
RAMI on April 8, 2016 at 7:58 am said:

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
Ken Puls on April 8, 2016 at 8:32 am said:

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.)
Ian Watkins on June 10, 2016 at 7:24 am said:

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
Ken Puls on June 10, 2016 at 7:52 am said:

Ian, that's odd, but I'm glad it's working for you! (I'm trying to rationalize to myself why that would be...)
James Stout on June 23, 2016 at 6:22 am said:

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
Ken Puls on June 23, 2016 at 7:40 am said:

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.
Jan on July 5, 2016 at 5:51 am said:

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
Ken Puls on July 8, 2016 at 11:01 am said:

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.
Mark on September 15, 2016 at 12:30 pm said:

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.
Ken Puls on September 19, 2016 at 1:02 pm said:

Honestly, I don't, no. I assume it's a relatively easy fix, it's just a pain is all?
Edward on July 11, 2017 at 9:20 pm said:

Hi Ken,

Can we update this code when the sheets are protected?

Thanks,
Mielkew
Ken Puls on July 12, 2017 at 10:38 am said:

Unfortunately tables won't update on protected worksheets. (Vote to change this here: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/16452913-get-tables-working-on-protected-sheets-add-rows)

For right now, you can add code to unprotect the worksheet, run the update, then reprotect the worksheet. It's a hack that leaves your password stored in VBA in clear text though, so use it with caution.
CH on July 20, 2017 at 5:01 pm said:

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!
Wouter De Raeve on August 24, 2017 at 9:38 am said:

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?
Ken Puls on August 24, 2017 at 5:13 pm said:

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. 🙁
Doug Burke on December 7, 2017 at 4:58 pm said:

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
Rebekah Sax on December 20, 2017 at 12:24 pm said:

Thanks Doug, that's great to hear. We're glad that you still found the solution helpful!
James Cartney on August 17, 2018 at 5:14 am said:

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
John on January 19, 2021 at 12:20 am said:

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.
Pingback: Refresh Power Query With VBA