Hi Guys

I have a summary workbook, that links to several password protected
workbooks. Currently when I open the summary workbook, it prompts me for the
password for every single protect workbook that it draws information from
(and all the passwords are different).

Is there a way in Excel 2007, to store the passwords to the other books, so
that I dont have to enter them everytime I open the summary book?

You could create a "helper" workbook that opens the receiving workbook but
without updating the links. Then the macro opens all the other workbooks and
immediately close them:

(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

(I got bored after 3 workbooks. You may want to test it with a couple to get it
going.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Paul Ferris wrote:

Hi Guys

I have a summary workbook, that links to several password protected
workbooks. Currently when I open the summary workbook, it prompts me for the
password for every single protect workbook that it draws information from
(and all the passwords are different).

Is there a way in Excel 2007, to store the passwords to the other books, so
that I dont have to enter them everytime I open the summary book?

--

Dave Peterson

Thanks Dave

So the help workbook I create is a new macro enabled workbook, and I open
it, and then it open the Summary book, and then the others?

Dave

I have created this Macro below, however when I run it, it still prompts me
for the password to the other workbooks (even though the passwords are set in
the myPasswords array)

Any ideas



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
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

When do you get prompted for the password?

When the sending files are being opened or after all are opened and closed and
the real workbook is recalculated?

If it's the second, then make sure calculation is set to automatic.

My thinking was that as soon as you opened one of the sending workbooks, then
the links in the real workbook would update those links.

If that's not right, then we could change the way things work.

We could open all of the other workbooks, then open the real workbook (and links
should be updated), then close all the other workbooks.





Paul Ferris wrote:

Dave

I have created this Macro below, however when I run it, it still prompts me
for the password to the other workbooks (even though the passwords are set in
the myPasswords array)

Any ideas

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
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

--

Dave Peterson

I open the helper workbook, and run the maco

It prompts me for the myRealWkbkName password and I enter that
It then opens the first protected workbook that it needs to read data from
for the "summary" book (in this case aaron.xlsx) and I am prompted for that
password)

The password is correct. If I do a copy and paste of the password from the
macro to workbook it opens, I just dont understand why then it is prompting
for the password

Sorry for being a newb

:)

You can include the password for the real workbook in the code, too:

Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0), _
password:="topsecret"

What password are you being prompted for when aaron.xlsx opens? If it's for
aaron.xlsx, then your password wasn't correctly supplied to the open statement.
(At least I've never seen that workbooks.open() statement fail when I supplied
the correct password.)

If aaron.xlsx has links to other protected workbooks, then you could either open
those protected workbooks and use updatelinks:=0 (just like in the "set
myrealwkbk =" statement).

Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr), _
Password:=myPasswords(iCtr), updatelinks:=0)







Paul Ferris wrote:

I open the helper workbook, and run the maco

It prompts me for the myRealWkbkName password and I enter that
It then opens the first protected workbook that it needs to read data from
for the "summary" book (in this case aaron.xlsx) and I am prompted for that
password)

The password is correct. If I do a copy and paste of the password from the
macro to workbook it opens, I just dont understand why then it is prompting
for the password

Sorry for being a newb

:)

--

Dave Peterson

ps. If you're being prompted while the macro is opening the sending workbooks,
then the passwords are incorrect for that workbook.

Paul Ferris wrote:

Dave

I have created this Macro below, however when I run it, it still prompts me
for the password to the other workbooks (even though the passwords are set in
the myPasswords array)

Any ideas

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
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

--

Dave Peterson

I open the helper workbook, and run the maco

It prompts me for the myRealWkbkName password and I enter that
It then opens the first protected workbook that it needs to read data from
for the "summary" book (in this case aaron.xlsx) and I am prompted for that
password)

The password is correct. If I do a copy and paste of the password from the
macro to workbook it opens, I just dont understand why then it is prompting
for the password

Sorry for being a newb

:)

Double checked the passwords - they are all correct

How about a test with smaller amount of workbooks and different passwords.

Create a new real workbook and two "sending" workbooks. And make the passwords
aaaa (or something easy).

Then test that.

Yep, I don't have a guess why it's not working for you. The code looks ok to
me.

In fact, how about just a single line test macro:

Option Explicit
sub aaaa()
workbooks.open filename:="F:\Security\Logging\Aaron.xlsx", password:="aar66"
Exit sub

I don't see why this would fail--except for typing mistakes.

Any chance that the password protected version of Aaron.xlsx isn't in
F:\security\logging. Maybe it's in a different folder??????


Paul Ferris wrote:

I open the helper workbook, and run the maco

It prompts me for the myRealWkbkName password and I enter that
It then opens the first protected workbook that it needs to read data from
for the "summary" book (in this case aaron.xlsx) and I am prompted for that
password)

The password is correct. If I do a copy and paste of the password from the
macro to workbook it opens, I just dont understand why then it is prompting
for the password

Sorry for being a newb

:)

Double checked the passwords - they are all correct

--

Dave Peterson

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

It was a long road, but you got there.

Congrats!!!

Paul Ferris wrote:

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