links in protected workbooks
 

In Excel 2007 I have five workbooks that are linked to a 6th workbook. Each
of the 5 workbooks is password protected to open. When I open the 6th
workbook, Excel requests that I provide the password of each of the 5
workbooks so the links will update, Is there a way around that.

links in protected workbooks
 

Somebody/something has to provide those passwords.

You could use a macro in a helper workbook that opens the 5 workbooks first,
then opens the "real" workbook. Then closes those 5 workbooks after the links
have been updated. (Yep, the macro has to supply the passwords for the first
5.)

Or you could use a macro in a helper workbook that opens the real workbook
(without updating links), then opens one workbook at a time and closes it
(allowing the links to update).

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")
'headers in row 1
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)


If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

carourke wrote:

In Excel 2007 I have five workbooks that are linked to a 6th workbook. Each
of the 5 workbooks is password protected to open. When I open the 6th
workbook, Excel requests that I provide the password of each of the 5
workbooks so the links will update, Is there a way around that.

--

Dave Peterson

links in protected workbooks
 

Hi carourke

The only wat is not to protect the workbooks
Or get the cell values from the 5 workbooks with code

--

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


"carourke" wrote in message ...
In Excel 2007 I have five workbooks that are linked to a 6th workbook. Each
of the 5 workbooks is password protected to open. When I open the 6th
workbook, Excel requests that I provide the password of each of the 5
workbooks so the links will update, Is there a way around that.

links in protected workbooks
 

In short, no.

Think about what you're asking. You've protected 5 workbooks from being able
to be viewed with a password. This is good, as it protects your info from
eyes that should see it. Now, you're wanting to have another workbook be able
to gather that info w/o having to provide a password! If this were possible,
the original 5 passwords would be useless as a nosy person would just create
a sepearte workbook that used formulas to link in all the data from the
protected workbook.

Now, perhaps you didn't actually mean to password protect the opening of a
workbook, just protect the structure. If you change this (Tools - Protection
- Protect Sheet) and unprotect the workbook from viewing (SaveAs - Tools -
Security, turn off password), then you could link the data in w/o having to
provide a password for each of the workbooks.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"carourke" wrote:

In Excel 2007 I have five workbooks that are linked to a 6th workbook. Each
of the 5 workbooks is password protected to open. When I open the 6th
workbook, Excel requests that I provide the password of each of the 5
workbooks so the links will update, Is there a way around that.

links in protected workbooks
 

The unprotected one is in a separate folder with restricted access, I was
trying not to have to create 5 separate folders with restricted access so I
put the 5 workbooks in one folder and then password protected them.

"Luke M" wrote:

In short, no.

Think about what you're asking. You've protected 5 workbooks from being able
to be viewed with a password. This is good, as it protects your info from
eyes that should see it. Now, you're wanting to have another workbook be able
to gather that info w/o having to provide a password! If this were possible,
the original 5 passwords would be useless as a nosy person would just create
a sepearte workbook that used formulas to link in all the data from the
protected workbook.

Now, perhaps you didn't actually mean to password protect the opening of a
workbook, just protect the structure. If you change this (Tools - Protection
- Protect Sheet) and unprotect the workbook from viewing (SaveAs - Tools -
Security, turn off password), then you could link the data in w/o having to
provide a password for each of the workbooks.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"carourke" wrote:

In Excel 2007 I have five workbooks that are linked to a 6th workbook. Each
of the 5 workbooks is password protected to open. When I open the 6th
workbook, Excel requests that I provide the password of each of the 5
workbooks so the links will update, Is there a way around that.