Linking through multiple sheets not updating...

I know this has been mentioned, but I can't find my exact situation in the
previous few days posts.

I have 5 excel documents. DocA.xls, DocB.xls, DocC.xls, DocD.xls, DocE.xls.

My accounting department has a very elaborate reporting/calculating string
of documents growing. They have over 100 documents all linked to each other
in the same directory.

DocA.xls is modified and it has a link to DocB.xls. DocB.xls uses
information froma linked field in DocA.xls to calculate a new field.
DocC.xls then uses this field to calcualte a field in DocD.xls. DocE.xls
then looks at DocD.xls to make the final calculation.

Problem:
Users report that if they make a change in DocA.xls and save, then open
DocE.xls that the calculation hasn't been changed by the modification just
made to DocA.xls. However, if they go and open all the other files
(DocB.xls, DocC.xls, DocD.xls) that DocE.xls will then have the correct
information.

Can anyone help me with this problem. I have never made a macro so if a
macro is required can you please give me a detailed explaination or tell me
where I can find it I also have found information on "Update Links" and
Tools - Options - Calculation tab, check to make sure "Update remote
reference is checked". I looks like these are checked.

Thank you.

Malic wrote:
Users report that if they make a change in DocA.xls and save, then open
DocE.xls that the calculation hasn't been changed by the modification just
made to DocA.xls. However, if they go and open all the other files
(DocB.xls, DocC.xls, DocD.xls) that DocE.xls will then have the correct
information.


That is entirely as I would expect.

You may want to rethink the way you are structuring this application to
reduce the number of levels of links or to use a database to hold the data.

If you want to ensure that all link sources at all levels are opened,
recalculated and closed when you open DocE.xls you would need a macro.

Open DocE.xls
Alt+F11 to the Visual Basic editor
Check that it is showing DocE.xls as the active project
Insert / Module
paste in the following:

Sub Auto_Open()
' get multiple levels of link sources updated after opening completed
Application.OnTime Now, "UpdateMyLinks"
End Sub

Sub UpdateMyLinks()
Application.ScreenUpdating = False
UpdateLinksIn ThisWorkbook
Application.ScreenUpdating = True
End Sub

Sub UpdateLinksIn(WB As Workbook)
Dim vLinks
Dim iLink As Integer
Dim wbSource As Workbook
vLinks = WB.LinkSources(xlExcelLinks)
If IsEmpty(vLinks) Then Exit Sub
For iLink = LBound(vLinks) To UBound(vLinks)
Set wbSource = Workbooks.Open(vLinks(iLink), ReadOnly:=True,
UpdateLinks:=0)
UpdateLinksIn wbSource
wbSource.Close saveChanges:=False
Next
Application.Calculate
End Sub

'---
Then close and save DOCE.xls
On opening DOCE.xls it should open all the linked workbooks for long enough
to get the information updated.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup