Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
Weird Links Behavior
This is one of the strangest problems I have encountered in Excel, and am
wondering if anyone has any words of wisdom regarding this issue. I have three files: File A, B, & C. File A is linked to file C. File B is linked to file C. When I open File A alone, it shows the correct linked values. When I open File B alone, it shows the correct linked values. But when I open File A, and then File B so both files are opened at the same time, the linked values in both of them change to a value that is not even in File C anymore. The only thing I can guess is that both files might be reverting to an old linked value somehow. Has anyone seen this before? Any ideas on what is causing it? I greatly appreciate your help; thank you. |
#2
Posted to microsoft.public.excel.links
|
|||
|
|||
Weird Links Behavior
Hi,
have you checked Edit, Links to see if A is linked to B or B to A? If this helps, please click the Yes button. "Jon Oakdale" wrote: This is one of the strangest problems I have encountered in Excel, and am wondering if anyone has any words of wisdom regarding this issue. I have three files: File A, B, & C. File A is linked to file C. File B is linked to file C. When I open File A alone, it shows the correct linked values. When I open File B alone, it shows the correct linked values. But when I open File A, and then File B so both files are opened at the same time, the linked values in both of them change to a value that is not even in File C anymore. The only thing I can guess is that both files might be reverting to an old linked value somehow. Has anyone seen this before? Any ideas on what is causing it? I greatly appreciate your help; thank you. |
#3
Posted to microsoft.public.excel.links
|
|||
|
|||
Weird Links Behavior
The options you have chosen for updating links in A and in B could be
relevant here. Those options are affected by: a) Tools Options Edit Ask to update automatic links b) Edit Links Startup Prompt (for each workbook) c) Whether you choose to update or not when asked d) If opening by program, the setting of the UpdateLinks argument e) The version of Excel (2007 has an additional 2 relevant options) f) Tools Options Calculation Save external link values (for each workbook) If you can advise us on all the above we may be able to reproduce the issue and help you further. I am aware that: - if you open A without updating links it will still have data from C from the last time A was saved after updating the links - if you then open B in the same instance of Excel without updating links it will also still have data from C from the last time B was saved after updating the links. - Excel then notices that there are two sets of data from C (which may differ) and decides in its wisdom that one is correct - and I can't recall whether it chooses A or B but I think it is A, resulting in changes in the linked data in the other workbook even though C has not been opened. To ensure the data from C is up to date you should always refresh the links on opening. I am also aware that there are occasional instances when refreshing links without opening the source file (C) does not work, although the circumstances in which this happens are not clear. In such cases you need to open the source file to get the links updated. You could do this automatically by using an Auto_Open macro in each of the workbooks A and B that refer to C: Sub Auto_Open() Application.OnTime Now, "Continue_Open" ' allow open to complete End Sub Sub Continue_Open() Application.ScreenUpdating = False Workbooks.Open ThisWorkbook.Path & "\C.xls", ReadOnly:=True ' this will update the link values Workbooks("C.xls").Close False Application.Calculate ' ensure all formulas are up to date. Application.ScreenUpdating = True End Sub Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#4
Posted to microsoft.public.excel.links
|
|||
|
|||
Weird Links Behavior
There are no links between A or B.
"Shane Devenshire" wrote: Hi, have you checked Edit, Links to see if A is linked to B or B to A? If this helps, please click the Yes button. "Jon Oakdale" wrote: This is one of the strangest problems I have encountered in Excel, and am wondering if anyone has any words of wisdom regarding this issue. I have three files: File A, B, & C. File A is linked to file C. File B is linked to file C. When I open File A alone, it shows the correct linked values. When I open File B alone, it shows the correct linked values. But when I open File A, and then File B so both files are opened at the same time, the linked values in both of them change to a value that is not even in File C anymore. The only thing I can guess is that both files might be reverting to an old linked value somehow. Has anyone seen this before? Any ideas on what is causing it? I greatly appreciate your help; thank you. |
#5
Posted to microsoft.public.excel.links
|
|||
|
|||
Weird Links Behavior
This is where Excel gets real weird. You wrote:
- Excel then notices that there are two sets of data from C (which may differ) and decides in its wisdom that one is correct - and I can't recall whether it chooses A or B but I think it is A, resulting in changes in the linked data in the other workbook even though C has not been opened. However, both A & B have the same numbers from C (all of which match) when each is opened up individually. But when A & B are opened up at the same time in the same Excel instance, they revert to a previous version of data from C that doesn't even exist in C anymore! In regards to your questions below: -I am using Excel 2003 -I have disabled the on-open link prompt, and links are not updated automatically -The Save External Link Values box is checked in all three files. Hope this helps, and thank you, Jon "Bill Manville" wrote: The options you have chosen for updating links in A and in B could be relevant here. Those options are affected by: a) Tools Options Edit Ask to update automatic links b) Edit Links Startup Prompt (for each workbook) c) Whether you choose to update or not when asked d) If opening by program, the setting of the UpdateLinks argument e) The version of Excel (2007 has an additional 2 relevant options) f) Tools Options Calculation Save external link values (for each workbook) If you can advise us on all the above we may be able to reproduce the issue and help you further. I am aware that: - if you open A without updating links it will still have data from C from the last time A was saved after updating the links - if you then open B in the same instance of Excel without updating links it will also still have data from C from the last time B was saved after updating the links. - Excel then notices that there are two sets of data from C (which may differ) and decides in its wisdom that one is correct - and I can't recall whether it chooses A or B but I think it is A, resulting in changes in the linked data in the other workbook even though C has not been opened. To ensure the data from C is up to date you should always refresh the links on opening. I am also aware that there are occasional instances when refreshing links without opening the source file (C) does not work, although the circumstances in which this happens are not clear. In such cases you need to open the source file to get the links updated. You could do this automatically by using an Auto_Open macro in each of the workbooks A and B that refer to C: Sub Auto_Open() Application.OnTime Now, "Continue_Open" ' allow open to complete End Sub Sub Continue_Open() Application.ScreenUpdating = False Workbooks.Open ThisWorkbook.Path & "\C.xls", ReadOnly:=True ' this will update the link values Workbooks("C.xls").Close False Application.Calculate ' ensure all formulas are up to date. Application.ScreenUpdating = True End Sub Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#6
Posted to microsoft.public.excel.links
|
|||
|
|||
Weird Links Behavior
I can't explain what is happening, and we won't get Microsoft to change
Excel 2003 so all I can suggest is that you avoid the situation either by not opening both A and B in the same instance of Excel or by having A and B quietly open and close C in the way I suggested before. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and Paste Date - weird behavior | Excel Discussion (Misc queries) | |||
weird macro behavior | Excel Discussion (Misc queries) | |||
Weird worksheet saving behavior | Excel Discussion (Misc queries) | |||
Excel weird behavior | Excel Discussion (Misc queries) | |||
Weird File Open/Save As Behavior | Excel Discussion (Misc queries) |