Links to password protected workbooks

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

Hi Michelle

Maybe you can use this add-in to get the values when you need them
There is a option to fill in the password

http://www.rondebruin.nl/merge.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Michelle" wrote in message ...
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

"Ron de Bruin" wrote in message
...
Hi Michelle

Maybe you can use this add-in to get the values when you need them
There is a option to fill in the password

http://www.rondebruin.nl/merge.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Michelle" wrote in message
...
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

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

Michelle wrote:

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

--

Dave Peterson

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Michelle" wrote in message ...
Thanks Ron - Will the add in allow me to store 20 passwords for all the
different files?

M

"Ron de Bruin" wrote in message
...
Hi Michelle

Maybe you can use this add-in to get the values when you need them
There is a option to fill in the password

http://www.rondebruin.nl/merge.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Michelle" wrote in message
...
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

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

"Dave Peterson" wrote in message
...
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

Michelle wrote:

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

--

Dave Peterson

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)



Michelle wrote:

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

"Dave Peterson" wrote in message
...
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

Michelle wrote:

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

--

Dave Peterson

--

Dave Peterson

Thanks - that's great

M

"Dave Peterson" wrote in message
...
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)



Michelle wrote:

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

"Dave Peterson" wrote in message
...
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

Michelle wrote:

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

--

Dave Peterson

--

Dave Peterson

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?

Spiky wrote:

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.

--

Dave Peterson