Links to password protected workbooks

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

Hello, I have a summary sheet which links to many (20ish) password protected
files. Every time I open it or update the links, I have to enter many
(20ish) passwords.

Is there a way I can avoid or automate this process.

I am happy to use VBA (or any other method), but bear in mind that the files
are big and take 10 seconds or so to open.

I'd really appreciate any feedback.

Many thanks

M
 
Thanks Ron - Will the add in allow me to store 20 passwords for all the
different files?

M
 
Maybe you could have another workbook contains a macro that opens the other 20
workbooks. The macro would need to know all the names and passwords for the
files.

(Saved from a previous post.)

Option Explicit
Sub testme()

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 = "C:\my documents\excel\book1.xls"

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

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

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

wkbk.Close savechanges:=False
Next iCtr

End Sub
 
No, if you have 20 passwords you need Dave's example

Make your life easier and use the same password or not use a password
Very easy to break the password
 
I like it, when should I run it? Is it in the Open event? Should I click
update links when the file opens?

How does it interface with the requirement to have the links updated when
the file opens?

Thanks

M
 
Use it to open all 21 workbooks. Don't open them yourself.

If you look at the code, it opens your "real" workbook first--but doesn't update
the links.

Then it opens each of the other 20 "sending" workbooks. After each of the
sending workbooks is opened, the links to that workbook will recalc. Then that
sending workbook is closed.

If these other 20 workbooks change while you have the real workbook open and you
want to refresh the links, you can run a macro that's almost exactly the same.

Just delete the line that opens the real workbook--you don't want that to happen
again.

This is the line to be removed:
Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0)
 
Thanks - that's great

M

Dave Peterson said:
Use it to open all 21 workbooks. Don't open them yourself.

If you look at the code, it opens your "real" workbook first--but doesn't
update
the links.

Then it opens each of the other 20 "sending" workbooks. After each of the
sending workbooks is opened, the links to that workbook will recalc. Then
that
sending workbook is closed.

If these other 20 workbooks change while you have the real workbook open
and you
want to refresh the links, you can run a macro that's almost exactly the
same.

Just delete the line that opens the real workbook--you don't want that to
happen
again.

This is the line to be removed:
Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0)
Click to expand...
 
Does anyone know why this happens? I will soon be writing similar
summary files, so it may be apropros for me. But when I test it, my
links update without requiring the passwords.
 
What did you protect and how did you protect it?

Did you use worksheet protection or workbook protection?
If workbook, did you use tools|Protect|protect workbook?
Or did you use File|SaveAs|tools|General options|Password to modify or password
to open?
 
Back
Top