Update queries in to a protected sheet.

[g3org]

Hello everyone.
It is possible to update a queries in to a protected sheet automatically or it is just a myth?


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

I have tried this way but I still got the error that the sheet is protected. And what I wanna to be automatically refreshed after 2 hours, or when I open the file? Any idea will be helpful!

Thank you.

 

[Alex Blakenburg]

You will need to unprotect and reprotect as you are doing but if it is not working then in addition to that try turning off Background refresh.

The link below will tell you how to do that:

How to Disable Background Refresh in Excel (2 Handy Methods)

Learn how to disable background refresh in Excel with easy methods including VBA code. Download practice workbook for free and enjoy learning!

www.exceldemy.com

 

[g3org]

That is working, if I run the Macro manually, with this code should not be running automatically when I open the file?

Private Sub Workbook_Open()
Sub RefreshmyQuery()
Sheets("Admin").Unprotect Password:="123"
ActiveWorkbook.Connections("Query - Admin").Refresh
Sheets("Admin").Protect Password:="123"

End Sub
End Sub



Thank you.