Update links from password protected workbooks (07

Topic

New Reply

Why don’t you open all 25 workbooks, update all links, then close the workbooks?

Reply | Quote

Replies

(Edited by HansV to provide link to post – see Help 19)

I have a project I have been asked to complete. We have a folder that contains about 25 workbooks that are each protected by their own unique password. I have been asked to create a summary workbook that contains the data from certain cells of each of these individual workbooks.
I have created a workbook with links to the data that I need to summarize, but each time I open the workbook I am asked to supply the password for each of the 25 files.
I found some code (post 675,462) in the lounge that will allow me to open each of the files, using a for/next loop but I am stumped on how to force the individual links to update while the workbook is open. Is there a piece of code that I can insert before the wkb.Close that will force the link to that workbook to update?

Thanks in advance for any help you can provide. The code I am using is below.

Private Sub Workbook_Open()
Dim wkb As Workbook
Dim rng As Range
Dim rCell As Range
Dim sPath As String
Dim sFile As String
Dim sPassword As String
Dim sSummary As String

‘set range for the path
Set rng = Range(“B:B25”)
Set sSummary = “Monthly_review_linked.xlsm”
For Each rCell In rng
sPath = rCell.Value
sPassword = rCell.Offset(0, 1).Value
sFile = rCell.Offest(0, 2).Value
Set wkb = Application.Workbooks.Open( _
Filename:=sPath, Password:=sPassword)

wkb.Close (False)
Next
Set rCell = Nothing
Set rng = Nothing
Set wkb = Nothing
End Sub

Reply | Quote

You can use the “UpdateLinks” parameter in the OPEN command

From XL 2002 VBA Help on Workbooks.Open

[indent]

---

UpdateLinks Optional Variant. Specifies the way links in the file are updated. If this argument is omitted, the user is prompted to specify how links will be updated. Otherwise, this argument is one of the values listed in the following table.

Value Meaning
0 Doesn’t update any references
1 Updates external references but not remote references
2 Updates remote references but not external references
3 Updates both remote and external references

---

[/indent]

So you would do something like:
Set wkb = Application.Workbooks.Open( _
Filename:=sPath, Password:=sPassword, _
UpdateLinks:=3)

to Update both remote and external references when each file is opened.
Steve

Reply | Quote

Thanks to all!

I got it to work.

Reply | Quote