uk_dave&gill
Board Regular
- Joined
- May 17, 2007
- Messages
- 79
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!)
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!)