Macro to open passworded workbooks

[uk_dave&gill]

I've got a workbook ("Overview") which needs to refer to other workbooks (actually staff timesheets) which sit on a shared drive. All of these are passworded so only the staff member (and myself) can access them

As the workbook opens, it reads the links from the other files, and thus I have to enter the passwords one by one to update the data. I have come up with a macro which should do the job as follows:-

Private Sub Workbook_Open()

MsgBox "OK to run macro?"
' Message is here to let me know if the macro has run
Application.Workbooks.Open "S:\Leave\Timesheets\Fred 2008.xls", True, False, , "abc", "abc"
Application.Workbooks.Open "S:\Leave\Timesheets\Bill 2008.xls", True, False, , "def", "def"
Application.Workbooks.Open "S:\Leave\Timesheets\Tom 2008.xls", True, False, , "ghi", "ghi"
Application.Workbooks.Open "S:\Leave\Timesheets\**** 2008.xls", True, False, , "jkl", "jkl"
Application.Workbooks.Open "S:\Leave\Timesheets\Harry 2008.xls", True, False, , "mno", "mno"

End Sub

This seems to work, as it opens the workbooks. However, it doesn't run until after I've been manually prompted to enter the passwords. I'm guessing that the workbook links are taking precedence over the macro?

Is there either:-

a) A way to update the data into Overview without having to actually force the timesheets to open?

b) A way to make the macro run before the links update?

(btw I've not compromised the user passwords as Overview is itself passworded, so no-one can view the code!)

 

[Andrew Poulsom]

Look here:

http://www.mrexcel.com/forum/showthread.php?t=168849

 

[Richard Schollar]

So you open all the source workbooks - is that correct? All you should have to do then is to open all the workbooks with the UpdateLinks:=False set for each Workbook.Open (have Automatic calauclation switced off to Manual whiclst doing this) and once opened turn the Automatic calculation back on. This will then update all the values using the opened workbook.

 

[uk_dave&gill]

I've tried Andrew's suggestion from the link he gave. This works in as much as the values update as soon as Overview is opened. However, it seems that the macro runs after I've clicked "cancel" to each of the password prompts, and when it runs, it wipes all the links (contained in formulas in cells D8:D12)!

I'd quite like to do the job without actually opening the other workbooks if possible Richard

 

[Andrew Poulsom]

Were you prompted to update links, and if so did you say No?

 

[uk_dave&gill]

Were you prompted to update links, and if so did you say No?

No, just tried it again and didn't get the prompt for update links....

 

[Andrew Poulsom]

Open a new workbook, choose Tools|Options|Edit tab and check Ask to update automatic links. Then try again.

 

[uk_dave&gill]

I get the prompt now, chose not to update, but it still wiped D8:D12!

 

[Andrew Poulsom]

Nothing in the code I posted is wiping out D8:D12.

 

[uk_dave&gill]

Nothing in the code I posted is wiping out D8:D12.

To be honest, I don't know much about coding, but I can see that too!

I'll have another look on Monday and see if I can see what's doing it - basically it's just running the code and then going rapidly down D8:D12. I tried locking these cells and protecting the worksheet, and it then did it from D13:D17, which is odd! Maybe I've managed to get some other code doing something it shouldn't be somewhere?!