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:
That’s it. Refresh will succeed.
Enjoy and stay queryious 😉