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_TE MPLATES\FY08\LRO_LABR_Executive Office Group.xls", _

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

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

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

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

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

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

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

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

"\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATE S\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







"hurlbut777" wrote:

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.