Linking to Password Protect Workbooks
 

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?

Linking to Password Protect Workbooks
 

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

Linking to Password Protect Workbooks
 

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?

Linking to Password Protect Workbooks
 

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

Linking to Password Protect Workbooks
 

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

Linking to Password Protect Workbooks
 

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

Linking to Password Protect Workbooks
 

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

:)

Linking to Password Protect Workbooks
 

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

Linking to Password Protect Workbooks
 

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

Linking to Password Protect Workbooks
 

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" wrote:

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

Linking to Password Protect Workbooks
 

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

Linking to Password Protect Workbooks
 

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

"Dave Peterson" wrote:

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

Linking to Password Protect Workbooks
 

OK

This macro works perfectly

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


"Dave Peterson" wrote:

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

Linking to Password Protect Workbooks
 

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)

Linking to Password Protect Workbooks
 

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

Paul Ferris wrote:

OK

This macro works perfectly

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

"Dave Peterson" wrote:

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


--

Dave Peterson

Linking to Password Protect Workbooks
 

I don't have a guess.



Paul Ferris wrote:

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

Linking to Password Protect Workbooks
 

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

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

Paul Ferris wrote:

OK

This macro works perfectly

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

"Dave Peterson" wrote:

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


--

Dave Peterson

--

Dave Peterson

Linking to Password Protect Workbooks
 

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

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

Linking to Password Protect Workbooks
 

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

Linking to Password Protect Workbooks
 

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)



Paul Ferris wrote:

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

Linking to Password Protect Workbooks
 

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

Linking to Password Protect Workbooks
 

Maybe opening the file in readonly mode would help.

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

Paul Ferris wrote:

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

Linking to Password Protect Workbooks
 

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

Linking to Password Protect Workbooks
 

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