A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Weird Links Behavior



 
 
Thread Tools Display Modes
  #1  
Old November 11th, 2008, 09:58 PM posted to microsoft.public.excel.links
Jon Oakdale
external usenet poster
 
Posts: 1
Default 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  
Old November 12th, 2008, 07:58 AM posted to microsoft.public.excel.links
Shane Devenshire[_3_]
external usenet poster
 
Posts: 3,333
Default 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  
Old November 12th, 2008, 08:44 AM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default 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  
Old November 13th, 2008, 02:35 AM posted to microsoft.public.excel.links
Jon Oakdale[_2_]
external usenet poster
 
Posts: 2
Default 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  
Old November 13th, 2008, 02:39 AM posted to microsoft.public.excel.links
Jon Oakdale[_2_]
external usenet poster
 
Posts: 2
Default 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  
Old November 13th, 2008, 09:44 AM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default 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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.