Update Password Protected Links

[james]

Hi,
I have the following button. It opens a spreadsheet that is password
protected. This spreadsheet has links to another password protected
spreadsheet. The button enters the password and sets update links to yes but
it then asks for a password to update the links I would like it to input the
password automatically. Can Anyone help please?

Thanks

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim ExcelApp As Object 'Excel.Application
Dim ExcelWkb As Object 'Excel.Workbook

Set ExcelApp = CreateObject("Excel.Application")

Set ExcelWkb = ExcelApp.Workbooks

ExcelWkb.Open "C:\Test.xls", , , , "ABCDE", UpdateLinks:=1

ExcelWkb.Close

ExcelApp.Quit

Set ExcelWkb = Nothing
Set ExcelApp = Nothing

Exit_Command1_Click:
Exit Sub
Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

 

[Dave Peterson]

Is there a reason that you're starting a second instance of excel instead of
just opening the file in the current workbook?


Somebody/something has to provide those passwords.

Saved from a previous post.

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.)

Maybe you could have another workbook contains a macro that opens the other xx
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 here:
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.)