Refresh Power Query With VBA

When I’ve finished building a solution in Excel, I like to give it a little polish, and really make it easy for my users to update it.  The last thing I want to do is to send them right clicking and refreshing everywhere, so I program a button to refresh Power Query with VBA.

The interesting part about the above statement is that Power Query doesn’t have any VBA object model, so what kind of black magic trick do we need to leverage to pull that off?  As it turns out, it’s very simple… almost too simple in fact.

A Simple Query

Let’s just grab the sample data file from my post on pulling Excel named ranges into Power Query.  Once we’ve done that:

  • Click in the blue table
  • Go to Power Query –> From Table
  • Let’s sort Animal ascending (just so we know something happened)
  • Next save and Exit the query

At this point, we should get a new “Sheet2” worksheet, with our table on it:

SNAGHTML34cf68

 The Required VBA Code

Next, we need to build our VBA for refreshing the table.  Rather than record and tweak a macro, I’m just going to give you the code that will update all Query Tables in the entire workbook in one shot.  But to use it, you need to know the secret handshake:

  • Press Alt + F11

This will open the Visual Basic editor for you.  If you don’t see a folder tree at the left, then press CTRL+R to make it show up.

  • Find your project in the list (It should be called “"VBA Project (Selecting Data.xlsx)”
  • Right click that name and choose “Insert Module”
  • In the window that pops up, paste in the following code:

Public Sub UpdatePowerQueries()
' Macro to update my Power Query script(s)

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

NOTE:  The code above was updated 2015-08-19 to reflect the method posted here:  http://www.excelguru.ca/blog/2015/08/19/update-refresh-power-queries-with-vba/

Now, I’ll admit that I find this a little looser than I generally like.  By default, all Power Query scripts create a new connection with the name “Power Query –“ the name of your query.  I’d prefer to check the type of query, but this will work.

Speaking of working, let’s prove it…  But first, close the Visual Basic Editor.

Proving The Refresh Works

The easiest way to do this is to go back to the table on Sheet 1 and add a new row to the table.  I’m going to do that first, then I’m going to:

  • Press Alt + F8
  • Choose “UpdatePowerQueries”
  • Click Run
  • Go back to Sheet2 to verify it’s updated

If all goes well,  you should now have another row of data in your table, as I do:

image

Adding Polish

Let’s face it, that’s probably harder than going to Data –> Refresh All.  The goal here was to make it easier for my users.  So let’s do that now.

  • Return to Sheet 1
  • Go to the Developer Tab (if you don’t see it, right click the ribbon, choose “Customize Ribbon” and check the box next to the Developer tab to expose it)
  • Click Insert and select the button in the top left

image

  • Left click and drag a button onto your worksheet

When you let go, you’ll be prompted to assign a macro.

  • Choose “UpdatePowerQueries” and click OK
  • While the dots are still on the corners, click in the text
  • Backspace it out and replace it with something helpful like “Update Queries” (if you click elsewhere, you’ll need to right click the button to get the selection handles back.)
  • Click in the worksheet to de-select the button

SNAGHTML57873a

That’s it.  Test it again by adding some more data to the table then clicking the button.

Ramifications

I write a lot of VBA to make my users lives easier, and generally use this kind of technique as part of a bigger goal.  But regardless, it can still be useful as a stand alone routine if you want to avoid having to train users on how to do things through the ribbon.

40 thoughts on “Refresh Power Query With VBA

  1. This is really useful, thanks!

    If i try and refresh my PQ tables with VBA I often (but frustratingly, not always!) get the error that my database is opened by 'Admin' on my machine even when the database is closed. I can either try the macro again (sometimes it works) or I can refresh each table manually by right clicking on each one.

    Do you have any idea why this could be happening and what I could do to try and solve it?

    Many thanks,

    Alice

  2. Hi Alice,

    It depends on what kind of database it is. If it's Access, it could be that another user is in the file and has it locked. Can you share what database you're trying to connect to?

  3. Great code Ken

    I have several power queries that I will execute usingthis approach. However, I need a mechanism whereby I can lock out the user from interrupting the queries as they are running. When each one refreshes a message in the status bar indicates so but also 'invites' the user to 'ESC to cancel'
    Normally I would use Application.EnableCancelKey = xlDisabled to prevent users breaking into code but this doesn't seem to work here
    I want to able to indicate to user that queries are refreshing but also stop them from cancelling as it sends them straight into this underlying code

  4. Hi Anthony,

    The issue here is that when you kick off the connection refresh, Excel immediately moves on to the next refresh, and fires it as soon as the previous one is done. With no "AfterConnectionUpdate" event, there's really no way to set up a trigger to monitor the last refresh, short of using a Wait procedure. That's obviously not ideal, as we don't know how long it will take a connection to refresh.

    We could use code similar to the following to disable the cancel key until immediately after the last connection is called:
    Dim cn As WorkbookConnection

    On Error GoTo ErrorExit
    Application.OnKey "{Esc}", ""
    For Each cn In ThisWorkbook.Connections
    If Left(cn, 13) = "Power Query -" Then cn.Refresh
    MsgBox "Refreshed!"
    Next cn

    ErrorExit:
    Application.OnKey "{Esc}"

    I'll leave it up to you to decide if you want to adjust it to disable the Cancel key until x seconds after the last refresh is kicked off.

  5. Hi Anthony, try this

    Data->Connections->
    Select the connection->Properties
    Uncheck the box "Enable background refresh"

    Regards

  6. Hi Ken
    I've built quite a large data model which loads up around 40 data files, each transformed using PowerQuery then loaded into data model. If I had to manipulate all of these files by VBA I'd be at it for months. PQ has made a mountainous task possible
    The thing that concerns me is that should any of the data files change in anyway then the respective workbook query will fail. This will be seen by looking in the PQ window. Some data files come from external clients so there is every chance one day some bright spark will decide to add a column. As the PQ is tightly bound to the data source it will just fail
    I have a range of data files and I would like to loop through triggering a refresh on each of them, one by one. I'd like to be able to post a message with success/failure to alert the user that a query has failed due a problem with one of the data files
    There doesn't appear to be a way of triggering an indivual query refresh then have VBA wait for it to complete before proceeding to the next one
    I can trigger a mass refresh but then you would have to look in the PQ window to check that all of have successfully executed
    Cheers
    Anthony

  7. Apologies, you've kind of indicated already that I'm trying to achieve isn't possible!
    It would be handy if a query could simply post the status you see in the window to a place more visible to user. I.e capture the query success status in VBA somehow

  8. Anthony, if you're going to pull directly into the model with Power Query, and you're at all concerned about the stability of the data, I HIGHLY recommend you take a look at Chris Webb's blog on dealing with data source errors: https://cwebbbi.wordpress.com/2014/09/18/handling-data-source-errors-in-power-query/

    I'd be implementing this right away. Next thing you could do is create a simple measure to COUNTROWS on each table in the Data model, and return them to a cube formula or pivot. This would give you a good indicator if something went wrong. (1 record = bad, more than one = good).

  9. Hi Ken
    Keen to have the core workbook / thin clients arrangement but no PowerPivot server side
    I haven't checked but I'm wondering whether it is possible to use power query in a different workbook to connect directly into a data model in another?
    If not (and it would be a great additional feature) then my only option is to export some reporting data sets in csv form from my core workbook to be consumed by thin client workbook
    Cheers
    Anthony

  10. Hi Anthony,

    Unfortunately I don't think that you can connect to the data model in another workbook, no. But you could connect to the same data sources using other workbooks, or even connect to a worksheet from another workbook (allowing you to consume a PivotTable output from a PowerPivot model in that workbook...)

  11. Pingback: Power Query – Controlling M Query Function with User Driven Parameters | Devin Knight

  12. Hi Ken,

    Just wondering if you have extended this into the PowerPivot data model in Excel 2010. Meaning can you update the Data Model in PowerPivot via VBA where the connections are using PowerQuery rather than direct connects to the sq

  13. I used Power Query to pull data from another excel file, transform it and then use the data to build a Dashboard.

    I need to send this dashboard out to 10 leads of 10 different business units with each having just their data every week. Their file should NOT have any other data present at all in the file.

    I'd like to be able to create the 10 different versions and maybe even send the mail out to them on 1 click. I can probably do that by filtering out the data in the table through VBA and refresh the pivots I have in the dashboard.

    Is there a way though using VBA, to tell power query to refresh data for a particular business unit?

  14. Not purely through VBA no, but if you pull in a parameter table, then you can read from Excel cells to dynamically filter the query. That would give you the ability to - via VBA - update the parameter cell, then refresh the query, giving you just that department.

  15. Super helpful thanks!!
    it was useful to help me find issues with my Long Running PowerQueries

    Public Sub UpdatePowerQueries()
    ' Macro to update my Power Query script(s)
    Dim TStart As Date
    Dim TEnd As Date

    Dim cn As WorkbookConnection

    For Each cn In ThisWorkbook.Connections
    If Left(cn, 13) = "Power Query -" Then

    Debug.Print cn
    TStart = Now
    cn.Refresh
    TEnd = Now
    Debug.Print CStr(DateDiff("s", TStart, TEnd)) + " Seconds"
    Debug.Print ""
    Debug.Print "----"
    End If
    Next cn
    End Sub

  16. Did not work for me. I have a Table called TABLE1 on sheet1 in a workbook. In the same workbook on sheet2 I have a table connected to a power query for TABLE1. I went to sheet1 and added a row and then went back to sheet two and ran this code. Nothing happened. the connection refreshed but the table based off the query did not update. I'm running Excel 2013 with Power Query with all the latest updates. Sigh.

  17. "Not purely through VBA no, but if you pull in a parameter table, then you can read from Excel cells to dynamically filter the query. That would give you the ability to - via VBA - update the parameter cell, then refresh the query, giving you just that department." - Didn't quite understand this, especially the "dynamically filter the query" part. Can you elaborate or point me to any blog post that explains the technique?

  18. Can this code be modified to run in a protected worksheet? Have tried running an unprotect code, then this code, then another to re-protect the worksheet but it never works for me. Even when adding delays in the code.

  19. Hey Carl,

    My thoughts are that the query is not finished loading before the protection is re-applied. What I'd try is re-protecting the workbook in VBA, but adding this to the end of the line:
    , userinterfaceonly=true

    That should set the protection to protect against users doing something, but still let the macro run.

  20. I am trying to achieve auto-scheduled update, by using Task Scheduler and another Workbook with macros.
    And the problem is - macros alone in the workbook with connections works fine, when I try to run this macros from another workbook by using Application Run and then check works it says "Download did not complete" for each of the query, despite computer was loaded in full, as when I update all manually or with macros in right workbook. Do you have any workaround?

    Macros in Model - workbook with queries:
    Option Explicit

    Public Sub UpdatePowerQueries()
    ' Macro to update my Power Query script(s)

    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

    Range("J7").value = "Updated - " & Date

    End Sub

    Macros in workbook "Macros" - that will be scheduled:
    Option Explicit
    Sub Auto_Open()
    '#### To stop the macro you can use keyboard = "ESC" ####'
    Call Main
    End Sub

    Sub Main()
    'Disabling some options for better performance during the macro execution'
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim strRootFolder As String
    Dim strSearchPath As String
    Dim strSearchName As String

    'Setting up the files path'
    strRootFolder = ThisWorkbook.Path & "\"
    strSearchPath = strRootFolder & "AP Search Multiyear.xlsm"
    strSearchName = "AP Search Multiyear.xlsm"

    Workbooks.Open strSearchPath
    Workbooks(strSearchName).Activate

    'Disabling some options for better performance during the macro execution'
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    '' Macro to update my Power Query script(s)
    Application.Run ("'" & strSearchName & "'!UpdatePowerQueries")

    ''http://www.excelguru.ca/blog/2014/10/22/refresh-power-query-with-vba/
    'Screen update
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Workbooks(strSearchName).Save

    'SAVE AND CLOSE EXCEL PHASE'
    Workbooks(strSearchName).Close False

    ThisWorkbook.Activate
    ThisWorkbook.Save
    Application.Quit

    End Sub

  21. Hi everyone!

    First of all, thanks Ken for your article... piece of cake 😉

    I have a similar code than Alex, the same issue and (sadly) the same result when the queries are updated automatically by a macro ("Download did not complete"), but they are successfully updated if the process is run manually.

    I have tried some ways to solve this issue as:

    - Including the sentence "DoEvents" after the update process in the macro.
    - Updating each connection in a loop instead of using "RefreshAll".
    - Using the sentence Application.Wait("Time") after the update process with a several amount of seconds.

    I know (as you said Ken) Power Query doesn´t provide any VBA object model but, is there any way to retrieve the status of a query of Power Query?

    I have been thinking that if we can get the status, we can repeat the update process until the data download is complete.

    Thanks in advance.

  22. Unfortunately, not that I'm aware of, no. The thing is that in Excel 2013 we're not actually refreshing Power Query. We're actually refreshing the Excel connection, which happens to kick off Power Query. Being that Power Query is an addin, there is no flag that gets pushed back to Excel to say "I'm done".

    My feeling is that the only method we have to attempt to insert control here is to possibly flip the setting on the connection to turn off background refresh. My hypothesis here is that it SHOULD prevent Excel from moving on before refreshing the subsequent connections, but I could be wrong there. I haven't had time to fool around with it and come up with a reliable solution, unfortunately.

  23. @Alex, does this work?

    If lTest > 0 Then
    cn.BackgroundQuery = False
    cn.Refresh
    End If

    Regards

  24. Sorry, I solved the problem, but forget to point it to you.
    I added some lines, than enable refresh (by default disabled and after macros as well) and wait until queries done:
    On Error GoTo ErrorExit
    Application.OnKey "{Esc}", ""
    For Each cn In ThisWorkbook.Connections
    If Left(cn, 13) = "Power Query -" Then cn.EnableRefresh = True
    cn.Refresh
    Application.CalculateUntilAsyncQueriesDone
    Next cn

    Application.CalculateFullRebuild
    Application.CalculateUntilAsyncQueriesDone

    'Application.ScreenUpdating = True
    For Each cn In ThisWorkbook.Connections
    If Left(cn, 13) = "Power Query -" Then cn.EnableRefresh = False
    Next cn

  25. @Alex I've run into the same issue and implemented the solution you provided but still receive the same results (i.e. "Download did not complete."). My code is below; do you see any issues?

    For Each cn In TDR.Connections
    If Left(cn, 13) = "Power Query -" Then cn.EnableRefresh = True
    cn.Refresh
    Application.CalculateUntilAsyncQueriesDone
    Next cn

    Application.CalculateFullRebuild
    Application.CalculateUntilAsyncQueriesDone

    'Application.ScreenUpdating = True
    For Each cn In TDR.Connections
    If Left(cn, 13) = "Power Query -" Then cn.EnableRefresh = False
    Next cn

  26. Is it important which query got refreshed first in your VBA loop if we have a chain of dependent queries? (E.g. Q3 is merge of Q1 and Q2 ,Q6 is Q5 append Q4 and then Q7 is merge of Q3 and Q6)

  27. Hi Leonid,

    To be honest, after learning a bit more about this approach I think I'd actually just automate the Data --> Refresh All process wherever possible. At that point the query sequencing will take care of itself, and you'll also find that it performs better, especially if you have a larger number of queries. (We have discovered that the data model gets cached after each refresh operation, so if you run the macro above it would refresh and cache 7 times vs doing the cache only once with the data --> refresh all approach.)

  28. Not sure if others faced this issue but just updated Power Query and the Connection String of my Power Query tables changed to: provider=Microsoft.Mashup.OleDb.1

    Note the lowercase "provider" which you'll have to edit in the VBA code to compensate for this.

  29. Well that's more than a bit frustrating, isn't it? I'm curious, did this change for existing connections or just new ones?

    We can pretty easily fix this by modifying the lTest line as follows:

    lTest = InStr(1, LCase(cn.OLEDBConnection.Connection), "provider=microsoft.mashup.oledb.1")

    But we really shouldn't need to do this...

  30. Hey Matt,

    I just checked this, and I can't repro what you're seeing here. I just created a brand new Power Query using Excel 2016 (the very latest insider build), and it's working as expected, returning an upper case "P" for provider...

  31. Just troubleshooted the issue and believe I figured out the cause...

    I had created a file in which I used Power Query in Excel 2010. I then opened this file in Excel 2013. For whatever reason this permanently changes the existing connection's string from uppercase to lowercase "provider"

    I edited your VBA code (removing "provider" altogether) to make sure I never face this issue again:
    InStr(1, cn.OLEDBConnection.Connection, "Microsoft.Mashup.OleDb.1")

    Do you foresee any issues with me doing this?

  32. I have text file with 2 million data I need to import to excel by power query.. before doing this there are 5 columns which 2 columns are dependent . how can I import only 5 columns and get distinct values from that columns.... the table has duplicate rows. and there is no unique row to identify. then user will be given drop down list. the list will be populated by unique rows from those 5 columns and user can select the values from the list and extract the data..... Note: those 5 columns will be there in the text file.

Leave a Reply

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