Update Protected Links

[Guest]

I have created a workbook to summarize data from other workbooks. In order
to accomplish this task, I linked data using formulas. I am trying to create
a macro that updates the links. Here's the catch, all of the other
workbooks that are linked have open passwords. So when I update the links I
have to key in multiple passwords. I am trying to get around this by simply
using vba to open up all the linked workbooks and then close them. However,
when I do this, the links do not update...any help on this would be
appreciated. End game, is I do not want to have to key any multiple
passwords everytime I need to update links...I would rather have code to that
for me.

 

[Guest]

I have found a way to open all the workbooks through vba code; however, I
cannot get the links to update. If I use vba code to the open the files, the
links will not update in the summary workbook, but if I open up the files
through the excel toolbar or windows explorer the links update fine. I am at
a loss on this one and need help. I have attached the vba code below
(grabbed from another post) and have tried to add an application.calculate
etc. to the code but still not working. HELP!!

Option Explicit
Sub Macro3()

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 = "\\Goant6\Account\Jeff_H\LRO\FY08\FY08 LRO Labor
Consolidation.xls"

myFileNames =
Array("\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATES\FY08\LRO_LABR_Executive Office Group.xls", _

"\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATES\FY08\LRO_LABR_Finance
Group.xls", _

"\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATES\FY08\LRO_LABR_Human
Resources Group.xls", _

"\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATES\FY08\LRO_LABR_Linehaul.xls", _

"\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATES\FY08\LRO_LABR_Maintc
Group.xls", _

"\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATES\FY08\LRO_LABR_Mexico
Group.xls", _

"\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATES\FY08\LRO_LABR_Operations
Support Group.xls", _

"\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATES\FY08\LRO_LABR_Transportation Dept.xls", _

"\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATES\FY08\LRO_LABR_Truckload
Services.xls", _

"\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATES\FY08\Ops SC LRO_FY08.xls")

myPasswords = Array("EXE9311", _
"finance", _
"HRS2892", _
"TRN7563", _
"MNT5510", _
"MEX2275", _
"OPS7862", _
"DIS7563", _
"CAP4466", _
"CCO7287")

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

Application.Calculate

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

wkbk.Close savechanges:=False
Next iCtr

End Sub

 

[Dave Peterson]

(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

 

[Guest]

Dave,

Thanks for your relply. Unfortunately, I already tried that code. For some
reason when I use vba code to open the files, the links will not update. If
I open up the file via non-code methods, the links update. I'm perplexed on
this one...guess I will just enter in 50 passwords everytime I want to
update...at this point that would have taken me less time than coming up with
an "efficient" method to automate.

Thanks again.

 

[Dave Peterson]

Do you have calculation set to manual when you run that macro?

Maybe adding an "application.calculate" line to the code will help.

If you recalc with the "sending" workbook open, then the links that point at
that file should update.

 

[Guest]

Dave,

I had also tried that as well. I have chalked it up to one of those random
excel problems. Thanks again for all the help.