If anybody is interested, while I haven't found an answer to my question I
did find a solution to my problem. First, to clarify the problem. It's not
the entire linked source that has the problem. It is only a select few
cells. From the Links menu, Check Status and Update Value do not correct the
problem. The only thing that seems to work is to open the source.

I don't know why I didn't find this before but there are LinkSources and
OpenLinks Methods. I am using Excel 2003 SP3. There is an example in
OpenLinks help that shows how to open all the linked workbooks. The code is
as follows:

Sub OpenAllLinks()
Dim arLinks As Variant
Dim intIndex As Integer
arLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(arLinks) Then
For intIndex = LBound(arLinks) To UBound(arLinks)
ActiveWorkbook.OpenLinks arLinks(intIndex)
Next intIndex
Else
MsgBox "The active workbook contains no external links."
End If
End Sub

I just took the code (not the entire Sub) and put it in my Auto_Open Sub.
There is a problem with this code. It opens the first linked workbook fine
but the ActiveWorkbook.OpenLinks command makes the linked workbook the active
workbook. So on the second pass through the loop it tries to open the second
link in the linked workbook and not in the original workbook. I corrected
this by changing both the ActiveWorkbook references to ThisWorkbook.

While this doesn't answer my questions or solve the problem the way I like,
it does keep my workbook useable for multiple users. I hope somebody else
finds this useful.

"Mark S" wrote:

I have a workbook that links information from about 6 other workbooks. The
other workbooks are in a read-only area from another department. The linked
information is simple text only cells.

Everything has worked fine for years, except on rare occassions. Once in
awhile the links to one of the workbooks won't work unless I open the source
workbook. This normally happens to me Sunday night and it disappears
sometime Monday. This time it happened over the weekend but now it's
Wednesday and I still have the problem.

Since this normally only happens on the weekend, I have always suspected
that it was due to a user leaving a workbook in some unknown state. I would
guess that they started a save but didn't finish it and left for the weekend.

I don't have acces to the individuals in the other deptartment so I can't
verify it that way. I could probably build a test on my own, but I was
wondering if anybody has seen this problem before. I assume different states
exist for an Excel workbook, is there a simple way to check?