Hi,

I am trying to create an efficient way to update links that I have from a
number of different workbooks. All these worksbooks are protected by unique
passwords. How does one get the password into the Update_Link command?
--
Tomas S

One doesn't.

You may want to consider opening your "receiving" workbook without updating the
links. Then open each of the other "sending" workbooks so the links could be
updated.

You could even build a macro to open the files in the correct order.

(Saved from a previous post.)

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

myRealWkbkName = "C:\my documents\excel\book1.xls"

myFileNames = Array("C:\my documents\excel\book11.xls", _
"C:\my documents\excel\book21.xls", _
"C:\my other folder\book11.xls")

myPasswords = Array("pwd1", _
"pwd2", _
"pwd3")

If UBound(myFileNames) < UBound(myPasswords) Then
MsgBox "check names & passwords--qty mismatch!"
Exit Sub
End If

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

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

If wkbk Is Nothing Then
MsgBox "Check file: " & myFileNames(iCtr)
Exit Sub
End If

wkbk.Close savechanges:=False
Next iCtr

End Sub

(I got bored after 3 workbooks. You may want to test it with a couple to get it
going.)

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


Tomas Stroem wrote:

Hi,

I am trying to create an efficient way to update links that I have from a
number of different workbooks. All these worksbooks are protected by unique
passwords. How does one get the password into the Update_Link command?
--
Tomas S

--

Dave Peterson

My problem is that when I just open the workbook all the links do not update
correctly, especially graphs...
I already have the macro to do this.
--
Tomas S


"Dave Peterson" wrote:

One doesn't.

You may want to consider opening your "receiving" workbook without updating the
links. Then open each of the other "sending" workbooks so the links could be
updated.

You could even build a macro to open the files in the correct order.

(Saved from a previous post.)

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

myRealWkbkName = "C:\my documents\excel\book1.xls"

myFileNames = Array("C:\my documents\excel\book11.xls", _
"C:\my documents\excel\book21.xls", _
"C:\my other folder\book11.xls")

myPasswords = Array("pwd1", _
"pwd2", _
"pwd3")

If UBound(myFileNames) < UBound(myPasswords) Then
MsgBox "check names & passwords--qty mismatch!"
Exit Sub
End If

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

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

If wkbk Is Nothing Then
MsgBox "Check file: " & myFileNames(iCtr)
Exit Sub
End If

wkbk.Close savechanges:=False
Next iCtr

End Sub

(I got bored after 3 workbooks. You may want to test it with a couple to get it
going.)

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


Tomas Stroem wrote:

Hi,

I am trying to create an efficient way to update links that I have from a
number of different workbooks. All these worksbooks are protected by unique
passwords. How does one get the password into the Update_Link command?
--
Tomas S

--

Dave Peterson

Maybe add an

Application.calculate
or
application.CalculateFull
or
application.CalculateFullRebuild

after each is opened

(Some of these may not be available in the version of excel you're running.)

Tomas Stroem wrote:

My problem is that when I just open the workbook all the links do not update
correctly, especially graphs...
I already have the macro to do this.
--
Tomas S

"Dave Peterson" wrote:

One doesn't.

You may want to consider opening your "receiving" workbook without updating the
links. Then open each of the other "sending" workbooks so the links could be
updated.

You could even build a macro to open the files in the correct order.

(Saved from a previous post.)

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

myRealWkbkName = "C:\my documents\excel\book1.xls"

myFileNames = Array("C:\my documents\excel\book11.xls", _
"C:\my documents\excel\book21.xls", _
"C:\my other folder\book11.xls")

myPasswords = Array("pwd1", _
"pwd2", _
"pwd3")

If UBound(myFileNames) < UBound(myPasswords) Then
MsgBox "check names & passwords--qty mismatch!"
Exit Sub
End If

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

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

If wkbk Is Nothing Then
MsgBox "Check file: " & myFileNames(iCtr)
Exit Sub
End If

wkbk.Close savechanges:=False
Next iCtr

End Sub

(I got bored after 3 workbooks. You may want to test it with a couple to get it
going.)

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


Tomas Stroem wrote:

Hi,

I am trying to create an efficient way to update links that I have from a
number of different workbooks. All these worksbooks are protected by unique
passwords. How does one get the password into the Update_Link command?
--
Tomas S

--

Dave Peterson


--

Dave Peterson