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" <mh_londonNOJUNK@hotmail.com> wrote in message news:B56C217E-B55D-40B7-87BF-DD276D513437@microsoft.com...
> 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" <rondebruin@kabelfoon.nl> wrote in message
news:O31jLb7qIHA.5940@TK2MSFTNGP04.phx.gbl...
> 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" <mh_londonNOJUNK@hotmail.com> wrote in message
> news:B56C217E-B55D-40B7-87BF-DD276D513437@microsoft.com...
>> 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" <mh_londonNOJUNK@hotmail.com> wrote in message news:256597FA-B3C6-4523-9B55-850C9A43A346@microsoft.com...
> Thanks Ron - Will the add in allow me to store 20 passwords for all the
> different files?
>
> M
>
> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
> news:O31jLb7qIHA.5940@TK2MSFTNGP04.phx.gbl...
>> 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" <mh_londonNOJUNK@hotmail.com> wrote in message
>> news:B56C217E-B55D-40B7-87BF-DD276D513437@microsoft.com...
>>> 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" <petersod@verizonXSPAM.net> wrote in message
news:481A13A7.9BBE45F1@verizonXSPAM.net...
> 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" <petersod@verizonXSPAM.net> wrote in message
> news:481A13A7.9BBE45F1@verizonXSPAM.net...
> > 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" <petersod@verizonXSPAM.net> wrote in message
news:481A5086.15A868F6@verizonXSPAM.net...
> 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" <petersod@verizonXSPAM.net> wrote in message
>> news:481A13A7.9BBE45F1@verizonXSPAM.net...
>> > 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