Allowing access to password protected spreadsheets

[DKerr]

I have a main spreadsheet which is linked to 6 other password protecte
spreadsheets used by junior staff. I know the passwords but I woul
like one of the junior staff to be able to open and update thi
spreadsheet without knowing the passwords. Can this be done

 

[Dave Peterson]

You could give that person a macro that opens the other workbooks via code.
(The passwords would be in that VBA Code.)

But I don't see the difference--if they open the workbook using the macro, then
they'll be able to update the workbook as they like.

 

[DKerr]

the individual isn't very knowledgeable on excel so I would be worrie
about them accessing the macro. Do you have an example

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim myFileNames As Variant
Dim myPasswords As Variant
Dim iCtr As Long
Dim wkbk As Workbook

myFileNames = Array("C:\my documents\excel\book11.xls", _
"C:\my documents\excel\book21.xls", _
"C:\my other folder\book11.xls")

myPasswords = Array("pwd1", _
"pwd2", _
"pwd3")

If UBound(myFileNames) <> UBound(myPasswords) Then
MsgBox "check names & passwords--qty mismatch!"
Exit Sub
End If

For iCtr = LBound(myFileNames) To UBound(myFileNames)
Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr), _
Password:=myPasswords(iCtr))
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox "Check file: " & myFileNames(iCtr)
Exit Sub
End If

Next iCtr

End Sub