Refreshing data on a locked workbook

[scottwilliams]

Hi all,

Long time reader, first time poster.

I have a bit of a VBA puzzle I am trying to clear up.
I have a data pack for managers to access, but do not want them touching the staff database behind this. I have password protected the workbook database and written the following code into 'this workbook' on the data pack


Sub Open_Protected_File()
Workbooks.Open Filename:= _
"O:\\Staff DB - Live V2.0 2018.xlsx", Password:="mypasswordhere"
Application.Wait (Now + TimeValue("0:00:01"))
ThisWorkbook.RefreshAll
Application.Wait (Now + TimeValue("0:00:01"))
ActiveWorkbook.Close SaveChanges:=False

End Sub

Originally, I did not have the Application wait lines in there, but added them to see if it makes a difference.

the problem I have, is this takes FOREVER. it hangs on 'retrieving data press esc to cancel : 100' for a very long time (over a minute or two at least) and then refreshes. anybody know of how to stop this hang? Also, does anybody know how to set this to automatically run when the workbook is opened? and finally... anybody know how to get rid of the error message that pops up at the start saying there is an error connecting to the file? or will this clear if/when the code or a version of it is automated at the start.

I am a bit of a VBA novice, and have just picked up these lines from the interweb of things... I don't like to ask for help, would rather figure it out myself, however I am stumped

thanks all

scott