Linking to Password Protect Workbooks

[Guest]

OK - Finally worked out that it is spreadsheets that someone else has opened
that I am being prompted for the passwords on

Is there a way to get around that - ie. if someone has the spreadsheet open
as the helper macro is opening them, it doesnt then prompt for the password

 

[Dave Peterson]

Maybe opening the file in readonly mode would help.

There's an example in one of the other branches.

 

[Guest]

Woo hoo! It works

For the record the macro ended up looking like


Option Explicit
Sub WorkBook_Open()
Call OpenSummary '<--- Name of pre-written macro
End Sub

Sub OpenSummary()

Dim myFileNames As Variant
Dim myPasswords As Variant
Dim iCtr As Long
Dim myRealWkbk As Workbook
Dim myRealWkbkName As String
Dim wkbk As Workbook

myRealWkbkName = "F:\Security\Logging\Security Averages.xlsx"

myFileNames = Array("F:\Security\Logging\Aaron.xlsx", _
"F:\Security\Logging\Aidan.xlsx", _
"F:\Security\Logging\Alfredo.xlsx", _
"F:\Security\Logging\Brad.xlsx", _
"F:\Security\Logging\Jacqui.xlsx", _
"F:\Security\Logging\Jenny.xlsx", _
"F:\Security\Logging\John.xlsx", _
"F:\Security\Logging\Kian.xlsx", _
"F:\Security\Logging\Louise.xlsx", _
"F:\Security\Logging\Michelle.xlsx", _
"F:\Security\Logging\Mirawati.xlsx", _
"F:\Security\Logging\Steve.xlsx")

myPasswords = Array("aar66", _
"aid11", _
"alf99", _
"brad33", _
"jacq77", _
"jen22", _
"john00", _
"kia55", _
"lou88", _
"mich77", _
"mira11", _
"sev44")

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

Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0)

For iCtr = LBound(myFileNames) To UBound(myFileNames)
Set wkbk = Nothing
On Error Resume Next

' MsgBox iCtr & vbLf & myFileNames(iCtr) & vbLf & myPasswords(iCtr)
Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr), _
Password:=myPasswords(iCtr), ReadOnly:=True)
On Error GoTo 0

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

wkbk.Close savechanges:=False
Next iCtr

End Sub



Thanks for all the help Dave - I couldnt have done this without your knowledge

 

[Dave Peterson]

It was a long road, but you got there.

Congrats!!!