[Mark S[_2_]]

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?

[Bill Manville]

A frequent reason for links showing #VALUE is if the workbook had been
saved using an earlier version of Excel than the one you are using.
Any help?

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

[Mark S]

That doesn't seem likely but I can try to look into it.

"Bill Manville" wrote:

A frequent reason for links showing #VALUE is if the workbook had been
saved using an earlier version of Excel than the one you are using.
Any help?

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

[Mark S]

Bill,

I should have been more clear. The cell actually indicates #N/A. The
information dialog indicates "Value Not Available Error".


"Bill Manville" wrote:

A frequent reason for links showing #VALUE is if the workbook had been
saved using an earlier version of Excel than the one you are using.
Any help?

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

[Bob Flanagan[_2_]]

Mark, it sounds like a network connection problem. Try selecting View,
Links, select the link that is not refreshing and do an update values.

If that doesn't work, try selecting the link and then doing a change source,
and just respecify the file.

If that solves, you may want to copy the network files to the same folder on
your PC and change your workbook to point at them. Obviously you would need
to copy the files each time....

Bob Flanagan
Macro Systems

http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Mark S" <Mark wrote in message
...
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?

[Mark S]

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?