update links to password protected workbooks.

[pjbur2005 via OfficeKB.com]

Dear all, I think from my research here and on other sites that i am trying
the impossible but I would like this confirmed or a solution please!

I have a user that has 30 workbooks each of which are password protected with
unique passwords, please note it is workbook not worksheet passords.

There is a summary workbook with links to cells in each of the 30 workbooks.
The user wants a command button theat will enable all links to be updated.
the problem is that when trying this the user is asked for the password.
Please see code I have so far:

Public Sub updateWBLinks()
Dim oWB As Workbook
thisbk = ActiveWorkbook.Name ' this is the summaray sheet.
dbfilepath = "I:\Social Services\HQ\Performance Management\Richard Henderson\
Hayling Island master v1.xls" ' this is the file to get data from -
password protected.
Application.DisplayAlerts = False
Set oWB = Workbooks.Open(Filename:=dbfilepath, UpdateLinks:=3, Password:
="se112", writerespassword:="se112")
MsgBox ActiveWorkbook.Name
Workbooks(thisbk).Activate
Workbooks(thisbk).UpdateLink Name:=ActiveWorkbook.LinkSources ' this causes
the password dialogue box to be displayed.
oWB.Close

For now I am only working with the one workbook am ok with a routine to open
all 30.

any ideas??


Many thanks
End Sub

 

[Dave Peterson]

Maybe this idea would work for you...

Saved from a previous post.

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.

This expects a worksheet named WkbkList that contains the names of the files in
A2:Axxx (headers in row 1) and passwords in B2:Bxxx. (The filenames have to
include the drive, path and name.)

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

'the workbook with all the links
myRealWkbkName = "C:\my documents\excel\book1.xls"

With Worksheets("WkbkList")
myFileNames = .Range("a2:b" & _
.Cells(.Rows.Count, "A").End(xlUp).Row).Value
End With

Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0)

For iCtr = LBound(myFileNames, 1) To UBound(myFileNames, 1)
Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr, 1), _
Password:=myFileNames(iCtr, 2), _
ReadOnly:=True)
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox "Check file: " & myFileNames(iCtr, 1)
Else
'the links should have updated when
'this workbook was opened.
'so we can close it here
wkbk.Close savechanges:=False
End If
Next iCtr

End Sub

If the files are all in the same folder, you don't have to include that in the
worksheet cells. You could just tell the program where to look:

Change this line:

Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr, 1), _
Password:=myFileNames(iCtr, 2), _
ReadOnly:=True)

to:

Set wkbk = Workbooks.Open(Filename:="C:\myfoldernamehere\" & _
myFileNames(iCtr, 1), _
Password:=myFileNames(iCtr, 2), _
ReadOnly:=True)