Links to password protected workbooks
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
|