VBA Password Popup If File Open

Hi.

Here's the scenario. I've got 10 staff who each have their own workbook. They put their figures in as they work, and I have created an overview workbook so I can see their work.
I've created a simple bit of code in VBA to pull the info from their workbooks and put it in my workbook as and when required, with a little button.
All the staff workbooks are password protected, so I've taken that into account in the code, and it works perfectly.

Unless...one of the staff has their workbook open at the time I press my little button, then I am prompted for their password. If 5 staff have their workbooks open then I am required to type 5 passwords.

My question then, is there a way to bypass asking for the password in their workbook is open, maybe having the workbook shared would be the answer? Another solution would be to force close their workbook (after saving it of course) and then running my code to extract the info I need? Is this possible?

Any help would be appreciated as I've struggled for a few days with this one.

Thanks.

Re: VBA Password Popup If File Open

The code is opening the worksheet. Here's an example -

Sub Button1_Click()
Dim y As Workbook
Dim x As Workbook
Dim ws As Worksheet
Dim sh As Worksheet
Dim rng As Range
Set y = ThisWorkbook
Application.ScreenUpdating = 0
Set x = Workbooks.Open("J:\File Path\Staff One.xlsx", Password:="randompassword")
Set ws = y.Sheets("Individual Total")
Set sh = x.Sheets("Monday")
Set rng = sh.Range("S2:AB2")
rng.Copy
y.Sheets("Individual Total").Range("D4").PasteSpecial xlValues
Set sh = x.Sheets("Tuesday")
Set rng = sh.Range("S2:AB2")
rng.Copy
y.Sheets("Individual Total").Range("D18").PasteSpecial xlValues
Set sh = x.Sheets("Wednesday")
Set rng = sh.Range("S2:AB2")
rng.Copy
y.Sheets("Individual Total").Range("D32").PasteSpecial xlValues
Set sh = x.Sheets("Thursday")
Set rng = sh.Range("S2:AB2")
rng.Copy
y.Sheets("Individual Total").Range("D46").PasteSpecial xlValues
Set sh = x.Sheets("Friday")
Set rng = sh.Range("S2:AB2")
rng.Copy
y.Sheets("Individual Total").Range("D60").PasteSpecial xlValues
Set sh = x.Sheets("Saturday")
Set rng = sh.Range("S2:AB2")
rng.Copy
y.Sheets("Individual Total").Range("D74").PasteSpecial xlValues
'Close x:
Application.CutCopyMode = False
x.Close
Sheets("Individual Total").Range("C4") = ""
End Sub

As you can see from line - Set x = Workbooks.Open("J:\File Path\Staff One.xlsx", Password:="randompassword") it's opening the workbook, and this works fine if the workbook is closed. If the staff member has it open however, I get the popup asking for the password, even though it's coded in there.
If I need to check if the worksheet is open, can I add something in there to do that, and if it is open, then extract the info I need?

Thanks in advance, as you can tell I'm a bit of a noob.