Multiple workbook user's with Master workbook - all password protected

Hello,

Using Excel 2000.
This is the situation...I have 17 different workbooks representing 17
people's salary all linking to a summary workbook for my Manager to
view. Each workbook is password protected and when opening up the
summary workbook it prompts you for a password for each of those
individual workbooks as the summary links to the individual sheets. Of
course should you decline the password in the summary workbook it does
not link the data. Is there anyway around this???



Yvon Aubé

Maybe you could provide a workbook that opens those 17 workbooks first
(providing the passwords in code). Then open your real workbook.

I used 3 workbooks in my sample code. You can add as many workbook names and
passwords as you want--keep them in order, though:

Option Explicit
Sub auto_open()

Dim wkbks() As Workbook
Dim wkbkNames As Variant
Dim wkbkPwd As Variant
Dim iCtr As Long
Dim testStr As String

wkbkNames = Array("C:\book1.xls", _
"C:\book2.xls", _
"c:\my documents\excel\book3.xls")

wkbkPwd = Array("Pwd1", _
"Pwd2", _
"Pwd3")

If UBound(wkbkNames) = UBound(wkbkPwd) Then
'ok
Else
MsgBox "Design error--number of passwords <> number of workbooks!"
Exit Sub
End If

ReDim wkbks(LBound(wkbkNames) To UBound(wkbkNames))

For iCtr = LBound(wkbkNames) To UBound(wkbkNames)
Set wkbks(iCtr) = Nothing
On Error Resume Next
Set wkbks(iCtr) = Workbooks.Open(Filename:=wkbkNames(iCtr), _
Password:=wkbkPwd(iCtr))
On Error GoTo 0

If wkbks(iCtr) Is Nothing Then
MsgBox wkbkNames(iCtr) & vbLf & "was not opened!"
End If
Next iCtr

Application.Calculate

'close the first 17 (total - 1 workbooks)
For iCtr = LBound(wkbks) To UBound(wkbks) - 1
If wkbks(iCtr) Is Nothing Then
'do nothing
Else
wkbks(iCtr).Close savechanges:=False
End If
Next iCtr

'ThisWorkbook.Close savechanges:=False
End Sub

When you're done testing (and after a save!), you can uncomment that .close
line. Then this workbook will open, then open the others, then close itself
(without saving!).

If all the workbooks were in the same folder, you could embed that folder name
in the .open line--instead of typing the path on each of the workbook names.

Kind of like:

Set wkbk = Workbooks.Open(Filename:="c:\my documents\" _
& wkbkNames(iCtr), _
Password:=wkbkPwd(iCtr))

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


==============
This area needs to be manipulated:

wkbkNames = Array("C:\book1.xls", _
"c:\my documents\excel\book2.xls")

Add 18 filenames to that list (17 linked + 1 real) and make sure that the last
workbook is the "real" workbook.



Yvon wrote:
>
> Hello,
>
> Using Excel 2000.
> This is the situation...I have 17 different workbooks representing 17
> people's salary all linking to a summary workbook for my Manager to
> view. Each workbook is password protected and when opening up the
> summary workbook it prompts you for a password for each of those
> individual workbooks as the summary links to the individual sheets. Of
> course should you decline the password in the summary workbook it does
> not link the data. Is there anyway around this???
>
> Yvon Aubé

--

Dave Peterson

Thanks Dave...worked like a charm! Y


Dave Peterson <[email protected]> wrote in message news:<[email protected]>...
> Maybe you could provide a workbook that opens those 17 workbooks first
> (providing the passwords in code). Then open your real workbook.
>
> I used 3 workbooks in my sample code. You can add as many workbook names and
> passwords as you want--keep them in order, though:
>
> Option Explicit
> Sub auto_open()
>
> Dim wkbks() As Workbook
> Dim wkbkNames As Variant
> Dim wkbkPwd As Variant
> Dim iCtr As Long
> Dim testStr As String
>
> wkbkNames = Array("C:\book1.xls", _
> "C:\book2.xls", _
> "c:\my documents\excel\book3.xls")
>
> wkbkPwd = Array("Pwd1", _
> "Pwd2", _
> "Pwd3")
>
> If UBound(wkbkNames) = UBound(wkbkPwd) Then
> 'ok
> Else
> MsgBox "Design error--number of passwords <> number of workbooks!"
> Exit Sub
> End If
>
> ReDim wkbks(LBound(wkbkNames) To UBound(wkbkNames))
>
> For iCtr = LBound(wkbkNames) To UBound(wkbkNames)
> Set wkbks(iCtr) = Nothing
> On Error Resume Next
> Set wkbks(iCtr) = Workbooks.Open(Filename:=wkbkNames(iCtr), _
> Password:=wkbkPwd(iCtr))
> On Error GoTo 0
>
> If wkbks(iCtr) Is Nothing Then
> MsgBox wkbkNames(iCtr) & vbLf & "was not opened!"
> End If
> Next iCtr
>
> Application.Calculate
>
> 'close the first 17 (total - 1 workbooks)
> For iCtr = LBound(wkbks) To UBound(wkbks) - 1
> If wkbks(iCtr) Is Nothing Then
> 'do nothing
> Else
> wkbks(iCtr).Close savechanges:=False
> End If
> Next iCtr
>
> 'ThisWorkbook.Close savechanges:=False
> End Sub
>
> When you're done testing (and after a save!), you can uncomment that .close
> line. Then this workbook will open, then open the others, then close itself
> (without saving!).
>
> If all the workbooks were in the same folder, you could embed that folder name
> in the .open line--instead of typing the path on each of the workbook names.
>
> Kind of like:
>
> Set wkbk = Workbooks.Open(Filename:="c:\my documents\" _
> & wkbkNames(iCtr), _
> Password:=wkbkPwd(iCtr))
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
>
> ==============
> This area needs to be manipulated:
>
> wkbkNames = Array("C:\book1.xls", _
> "c:\my documents\excel\book2.xls")
>
> Add 18 filenames to that list (17 linked + 1 real) and make sure that the last
> workbook is the "real" workbook.
>
>
>
> Yvon wrote:
> >
> > Hello,
> >
> > Using Excel 2000.
> > This is the situation...I have 17 different workbooks representing 17
> > people's salary all linking to a summary workbook for my Manager to
> > view. Each workbook is password protected and when opening up the
> > summary workbook it prompts you for a password for each of those
> > individual workbooks as the summary links to the individual sheets. Of
> > course should you decline the password in the summary workbook it does
> > not link the data. Is there anyway around this???
> >
> > Yvon Aubé