Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.links
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   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 3,346
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   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
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   Report Post  
Posted to microsoft.public.excel.links
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   Report Post  
Posted to microsoft.public.excel.links
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   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy and Paste Date - weird behavior matelot Excel Discussion (Misc queries) 3 February 23rd 07 06:20 PM
weird macro behavior Dave F Excel Discussion (Misc queries) 0 November 30th 06 04:35 PM
Weird worksheet saving behavior siouxland Excel Discussion (Misc queries) 0 October 12th 06 02:22 PM
Excel weird behavior [email protected] Excel Discussion (Misc queries) 4 May 25th 06 08:23 PM
Weird File Open/Save As Behavior [email protected] Excel Discussion (Misc queries) 0 December 9th 05 03:26 AM


All times are GMT +1. The time now is 09:35 PM.

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

About Us

"It's about Microsoft Excel"