Excel - Link to protected file

[D-niss]

I have hundred of files to which I want to link specific cells in one sheet.
The thing is, all of those file are password protected.

Now obviously when I open my sheet, I get hundreds of prompt for passwords.

Fortunately, the password is the same for all the sheets.
Is there a way I supply by code or else the requested password?

Does anyone have a suggestion on how I should go about this?


TIA
Dniss

[RobDog888]

Yes, you can pass the password to the Workbook during an Unprotect method call.
If this is from VB6 that you are automating this then...

Option Explicit
'Add a reference to MS Excel xx.0 Object Linrary
Private Sub Command1_Click()
    Dim oApp As Excel.Application
    Dim oWB As Excel.Workbook
    Set oApp = New Excel.Application
    oApp.Visible = True
    Set oWB = oApp.Workbooks.Open("C:\Book1.xls")
    oWB.Unprotect "Password"
    'Do your stuff
    '...
    '...
    oWB.Close True 'Save changes
    Set oWB = Nothing
    oApp.Quit
    Set oApp = Nothing
End Sub

Now if your Sheet is all that has the protection then you can do something similar but for the Sheet object.

[D-niss]

Hi there Rob, thanks for your quick reply.

I did see your answer to the other post. You code appears to be
opening the source file and supplying the pw, which is not what
I'm doing.
I don't want to open the source file, I want to update links in one file
that are referring to passworded files without having to type in each
time the password.

I hope this is more explicit.

TIA

[RobDog888]

But the other linked files are not open correct?
Then its the formula that contains the references to the other workbook/sheets.

Sorry for the confusion, but I got a headache this morning

[D-niss]

Correct, none of the source files are openned.

I guess I was looking into some trigger event for the "update link"
that would allow me to supply the password by code.

Now I guess I'll have to look into some kind of ADO connection.

Thanks for trying nevertheless.

[D-niss]

Can't do it with ADO either. Darn that's a tough one.

[Br1an_g]

i think that the linked files 'are' actually opened, but background processed.

so, taking this into account, if you open the file, and do not update links, then use robdoggs code to open each sheet with password, but background process them.

e.g.

Application.ScreenUpdating = false

maybe that would work?

[D-niss]

Yes I understood what he meant.
I believe this is the only way to go, aside from removing all pw.

I thought there could perhaps be some kind of custom event that
could intercept the pw request. Even the workbook_activate event
comes after...

And humm I'm not sure about the sheet actually being opened in
the backgroud, perhaps you're right but I have my doubts.

Thanks all the same.

[D-niss]

I guess I did not fully understand the implications. I thought I could perhaps create some kind of custom event. Thanx Rob you were right, it appears to be the only way to do it.

This help me alot also:
http://support.microsoft.com/?kbid=214314

What I ended up doing is using a sheet where I list all filename in col A, and it's filepath in col B.

I created an input box for the user to enter the pw once (triggered by Workbook_Open event) and proceed to load all the files, reading col A:B for name and path.

Here's my main routine:
========================
Option Explicit
Function UpdateLink(Pw As String)
On Error GoTo Err_UpdateLink
Dim Cell As Range
Dim i As Integer
Dim oldStatusBar As Boolean

PWForm.Hide

Application.ScreenUpdating = False
Application.DisplayStatusBar = True
oldStatusBar = Application.DisplayStatusBar
Application.StatusBar = "Updating links..."

i = 0

With Sheets("FileList")
Set Cell = .Range("A2")

Do While Cell.Offset(i, 0).Value <> ""
Workbooks.Open Filename:=Cell.Offset(i, 1) & "\" & Cell.Offset(i, 0), UpdateLinks:=3, ReadOnly:=True, Password:=Pw
Application.StatusBar = "Updating link..." & Cell.Offset(i, 0)
Workbooks(Cell.Offset(i, 0).Value).Close
i = i + 1
Loop

End With

Exit_Err_UpdateLink:
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar
Application.ScreenUpdating = True
Exit Function

Err_1004:
MsgBox "Wrong password, links not updated"
Resume Exit_Err_UpdateLink


Err_UpdateLink:
If Err.Number = 1004 Then GoTo Err_1004
MsgBox "Error finding file " & Cell.Offset(i, 0)
Resume Exit_Err_UpdateLink

End Function
========================================

Comments and suggestions are welcome!
Thanks