Linking to Password Protect Workbooks

[Paul Ferris]

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]

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]

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?

 

[Paul Ferris]

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]

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.

 

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

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

 

[Paul Ferris]

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]

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]

I am being asked for the aaron.xlsx password (even though it is correct
(aar66))

If I manually enter it when prompted, then I will be asked for the
Aidan.xlsx password, and so on

 

[Dave Peterson]

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]

I will try and let you know

It is definitely in that folder, as enter the password manually when
prompted opens the file

Thanks for all the help. Looks like this is a interesting "feature" of excel

 

[Paul Ferris]

OK

This macro works perfectly

Sub aaaa()
Workbooks.Open Filename:="F:\Security\Logging\Aaron.xlsx", Password:="aar66"
End Sub

 

[Paul Ferris]

But if I try this

Sub AllBooks()
Workbooks.Open Filename:="F:\Security\Logging\Aaron.xlsx", Password:="aar66"
Workbooks.Open Filename:="F:\Security\Logging\Aidan.xlsx", Password:="aid11"
Workbooks.Open Filename:="F:\Security\Logging\Alfredo.xlsx", Password:="alf99"
Workbooks.Open Filename:="F:\Security\Logging\Brad.xlsx", Password:="brad33"
Workbooks.Open Filename:="F:\Security\Logging\Jacqui.xlsx", Password:="jacq77"
Workbooks.Open Filename:="F:\Security\Logging\Jenny.xlsx", Password:="jen22"
Workbooks.Open Filename:="F:\Security\Logging\John.xlsx", Password:="john00"
Workbooks.Open Filename:="F:\Security\Logging\Kian.xlsx", Password:="kia55"
Workbooks.Open Filename:="F:\Security\Logging\Louise.xlsx", Password:="lou88"
Workbooks.Open Filename:="F:\Security\Logging\Michelle.xlsx",
Password:="mich77"
Workbooks.Open Filename:="F:\Security\Logging\Mirawati.xlsx",
Password:="mira11"
Workbooks.Open Filename:="F:\Security\Logging\Steve.xlsx", Password:="sev44"
End Sub

Then I start getting prompted on the third book (no matter which one I make
the third)

 

[Dave Peterson]

I don't see why this line wouldn't work then:

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

Maybe you could add a line right before it:


msgbox ictr & vblf & myfilenames(ictr) & vblf & mypasswords(ictr)

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

Just to verify that you've got things matched up correctly.

(Yep, it's a shot in the dark!)

 

[Dave Peterson]

I don't have a guess.

But if I try this

Sub AllBooks()
Workbooks.Open Filename:="F:\Security\Logging\Aaron.xlsx", Password:="aar66"
Workbooks.Open Filename:="F:\Security\Logging\Aidan.xlsx", Password:="aid11"
Workbooks.Open Filename:="F:\Security\Logging\Alfredo.xlsx", Password:="alf99"
Workbooks.Open Filename:="F:\Security\Logging\Brad.xlsx", Password:="brad33"
Workbooks.Open Filename:="F:\Security\Logging\Jacqui.xlsx", Password:="jacq77"
Workbooks.Open Filename:="F:\Security\Logging\Jenny.xlsx", Password:="jen22"
Workbooks.Open Filename:="F:\Security\Logging\John.xlsx", Password:="john00"
Workbooks.Open Filename:="F:\Security\Logging\Kian.xlsx", Password:="kia55"
Workbooks.Open Filename:="F:\Security\Logging\Louise.xlsx", Password:="lou88"
Workbooks.Open Filename:="F:\Security\Logging\Michelle.xlsx",
Password:="mich77"
Workbooks.Open Filename:="F:\Security\Logging\Mirawati.xlsx",
Password:="mira11"
Workbooks.Open Filename:="F:\Security\Logging\Steve.xlsx", Password:="sev44"
End Sub

Then I start getting prompted on the third book (no matter which one I make
the third)

 

[Dave Peterson]

There's an "on error resume next" line above the workbooks.open() line.

Try commenting that line (put an apostrophe in front of it). Maybe there's a
different error that's being masked.

 

[Dave Peterson]

Maybe it's not excel.

Is F: a network drive?

If you create a temporary folder on a local drive (C:\PaulTest) and copy those
files there, does it work ok?

Workbooks.Open Filename:="C:\paultest\Aaron.xlsx", Password:="aar66"
....all of them...

 

[Paul Ferris]

OK so this is just getting weird

Adding the echo, I get prompted for the password on the Alfredo.xlsx,
Brad.xlsx, Jacqui.xlsx workbooks - even though the password is correct.

All the other it works fine for

Could it be that someone else has these books open (the users), and because
they are already open I am being prompted?

Is there a way around that

 

[Dave Peterson]

I would have guessed that you would have gotten a "file already in use" prompt
and would have been asked if you wanted to open the file readonly.

You can avoid it with:

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