How to refresh Power Queries on protected sheets in Excel

When working with Power Query in Excel you might want to refresh Power Queries on protected sheets. But this will not work by default. Using a macro to temporarily unprotect the sheet and protect it again will do the trick. But this requires the password being displayed in the VBA code. So please have in mind that this technique only works for scenarios where you want to prevent accidental changes with the password protection.

Steps to refresh Power Queries on protected sheets

The following VBA code will unprotect the sheet “mySheet”, then refresh the query “myQuery” before protecting the sheet again with the password “myPassword”.

Sub RefreshmyQuery()
Sheets("mySheet").Unprotect Password:="myPassword"
ActiveWorkbook.Connections("Query - myQuery").Refresh
Sheets("mySheet").Protect Password:="myPassword"
End Sub

But if you use it as it is, you’ll receive the following error message:

The re-protection of the worksheet will kick in sooner than the refresh could finish.

To overcome this, you have to disable background refresh of your Power Query (“myQuery”). This can be done via the properties like so:

Disable background refresh then:

That’s it. Refresh will succeed.

Enjoy and stay queryious 😉

Comments (4) Write a comment

  1. Hi Imke,
    I came across this same issue a few weeks ago. I figured out that you can do disable background refreshes of Power Query connections via VBA too by using the “BackgroundQuery” property.
    The code below is your VBA solution with the added code.
    Always good to have multiple ways to tackle a problem

    Regards
    Shay

    Sub RefreshmyQuery()
    Sheets(“mySheet”).Unprotect Password:=”myPassword”

    With ThisWorkbook.Connections("Query - myQuery").OLEDBConnection
    RefreshState = .BackgroundQuery 'Save the current BackgroundQuery state
    .BackgroundQuery = False 'Turn off BackgroundQuery
    .Refresh 'Refresh the query
    .BackgroundQuery = RefreshState 'Restore the BackgroundQuery state
    End With

    Sheets("mySheet").Protect Password:="myPassword"

    End Sub

    Reply

  2. Hey peeps, thanks for this. Do you have any advice if the above is returning a subscript out of range message against the Connections line?

    Reply

Leave a Reply