%3C%2Ftext%3E%3C%2Fsvg%3E)
%3C%2Ftext%3E%3C%2Fsvg%3E)
%3C%2Ftext%3E%3C%2Fsvg%3E)
%3C%2Ftext%3E%3C%2Fsvg%3E)
To protect or limit the Excel spreadsheet, we can set a password to protect the Sheet. But when we password protect Sheet. We will not be able to Refresh Power Query. To do this we need VBA.
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
You copy this code into microsoft visual basic. Change the names to suit you according to the locations you have marked.
Code:
Sub RefreshmyQuery()
Sheets("Result").Unprotect Password:="YourPassword"
ActiveWorkbook.Connections("Query - Table1").Refresh
Sheets("Result").Protect Password:="YourPassword"
End Sub
But if you use it as it is, you’ll receive the following error message:
To overcome this, you have to disable background refresh of your Power Query (“myQuery”). This can be done via the properties like so:
Ok. Now you can Refresh Power Query
You can also use the code below to not have to disable the Power query backgroundSub RefreshmyQuery()
Sheets(“YourSheet”).Unprotect Password:=”YourPassword”
With ThisWorkbook.Connections("Query - YourQuery").OLEDBConnection
RefreshState = .BackgroundQuery
.BackgroundQuery = False '
.Refresh
.BackgroundQuery = RefreshState 'Restore the BackgroundQuery state
End With
Sheets("YourSheet").Protect Password:="YourPassword"
End Sub
Comments
Hãy là người đầu tiên để lại ý kiến về bài viết này!
Để lại bình luận
Địa chỉ email của bạn sẽ được bảo mật.