|
|
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.
|
|
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:
[Quoted Text] > 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.
|
|
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
|
|
There are no links between A or B.
"Shane Devenshire" wrote:
[Quoted Text] > 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.
|
|
This is where Excel gets real weird. You wrote:
[Quoted Text] > - 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
>
>
|
|
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
|
|
|