Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
Link - Value Not Available
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? |
#2
Posted to microsoft.public.excel.links
|
|||
|
|||
Link - Value Not Available
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 |
#3
Posted to microsoft.public.excel.links
|
|||
|
|||
Link - Value Not Available
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 |
#4
Posted to microsoft.public.excel.links
|
|||
|
|||
Link - Value Not Available
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 |
#6
Posted to microsoft.public.excel.links
|
|||
|
|||
Link - Value Not Available
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PASTE LINK option not available when I select PASTE SPECIAL to link an image in Excel to a Word document. | Links and Linking in Excel | |||
if i sort cell that has link to another page how to keep link | Excel Discussion (Misc queries) | |||
if i sort cell that has link to another page how to keep link | Excel Discussion (Misc queries) | |||
Link to external link | Excel Worksheet Functions | |||
Link | Links and Linking in Excel |