Looking for Information on How Links Work
Paul Sardella
I am in search of documentation that will explain how links between
workbooks are maintained. I continually have problems with two workbooks
that have links to four other workbooks. I will manually update the
links in the two workbooks to the lists in the other four workbooks -
then save the two workbooks.
Yet, periodically when I open one (of the two) workbooks, I believe that
the linking has reverted to a previous update. When I open the second
workbook, not all of the links point to the correct cells. Hence, I will
have to update the links again. Nothing damaged in my spreadsheets (as
far as I can tell), but very annoying. I'd like to correct this problem,
if possible.
I had tried posting a similar question about six weeks back in
microsoft.public.excel.links, but was not availed a satisfactory answer,
so I'm trying the programmng group (I don't believe that this is a
programming issue, although my workbooks contain a lot of code).
Suggestions at that time was that there were circular references and/or
to combine the workbooks. I haven't found circularity. The workbook
structure I have now causes very few system crashes - with the prior
setup of fewer workbooks, Excel was crashing more frequently.
Any help is appreciated.
Thanks in advance
Paul Sardella
Tom Ogilvy
='C:\My Documents\[Mybook1.xls]Sheet1'!$A$1
generally doesn't change unless both workbooks are opened and changes are
made to Mybook1.xls (such as a row insertion or deletion) that would cause
it to change.
It might be helpful to show what your links look like - examples of ones
that change and a before and after example.
I don't think there is a "known problem" with links changing on their own.
Regards,
Tom Ogilvy
"Paul Sardella" <PSar...@cheerful.com> wrote in message
news:3AC4AF9F...@cheerful.com...
Paul Sardella
Thanks for your input. Over the weekend I updated the links to a file named
Bear Bones.xls, which in turn pointed to the correct information in Streamer
Recap.xls. I usually don't open these two workbooks up together; I just
amnually update the links. I know that all was okay, because I run a
subroutine each weekend that updates information in another file - I spot
check the output from this subroutine for accuracy as I depend on the
information for trading stocks.
I opened Bear Bones this morning, and again the wrong information was shown.
In this instance, I have Bear Bones opened on one computer (Pii) and Streamer
Recap opened on another computer (Piii) - Pii and Piii are networked together.
Both files are stored on Piii's hard drive. However, the problem occurs with
another pair of files (two different files) that I only open on one machine
(Piii) - I only open the second file of this pair when the linked data is
displayed as inaccurate.
='\\Pentium iii\Stock Watch\[Streamer Recap.xls]Streamer'!D1173 is the
structure of the link. What I have is a vertical list of 1200 such links (D3
to D1202) that point to a list of stock symbols in another workbook. I
periodically change entries in this list (usually once a week). Therefore, I
should have only one stock symbol per link. This a.m., ZRAN shows 7 entries
(spread is D1138 to D1198), YHOO shows 3 entries, ZQK 5 entries, etc.
Therefore, my spreadsheet is not showing the symbols that were added in the
last month or so.
Now, I manually update the links to Streamer Recap.xls. I watch the sheet and
see the correct linking occur (in progress). I spot checked ten entries,
comparing the two lists - all seems okay. Sometime in the future, this process
of inaccruate "pointing" will happen again. Not necessarily the next time that
I open Bear Bones.
I have other files that are linked together, yet I have no problems with the
others. The two files (which store data) with which I continually have
problems have named ranges that are 1202Rows by 88Columns, and 1202Rows by
65Columns. Any other files that I use for linking contain named ranges with
many rows, but few columns - the "worst" case scenario in the non-problematic
data files is 10,000Rows by 6Columns. This leads me to believe that the
problem relates to the amount of columns used (or the structure of a matrix?).
Again, Thanks in advance
Paul Sardella
---------------------------------------
Tom Ogilvy
Bones.xls to another workbook named Streamer Recap.xls. Streamer Recap.xls
contains a list of stock symbols.
Now you open Streamer and make changes - I am assuming adding or deleting
cells/rows).
Later you open Bear Bones.xls and in the cells with the links, many of the
symbols are incorrect.
The Bear Bones.xls entries do not change just because you changed Streamer
Recap.xls. The links in Bear Bones.xls do nothing during periods when the
workbook is closed. The become active only when the workbook is opened.
Excel does not maintain an internal record to know that there is any
relationship between the two files - in fact Streamer Recap.xls knows
nothing about Bear Bones' links - So when you open Streamer Recap.xls, and
change where data is located, this has no effect on Bear Bones.xls or its
links. You close Streamer Recap.xls and later open Bear bones.xls - the
links are pointing at the old locations and pick up whatever information is
there when the links are updated on opening the workbook. To update the
links excel goes to Streamer Recap.xls opens it (although not in Excel),
accesses the refered to addresses (such as cell D1202 on sheet Streamer).
Whatever is there, is what Excel gets - it doesn't check some record of
where information has moved to and it doesn't care what is in the cell - it
just fetches whatever is there.
If the only changes you made were to edit the value in the existing cells
and D1202 would still be the appropriate location to get the information,
then there is no problem - when the link is updated it gets the updated
information - but if you change where the appropriate information is located
and Bear Bones.xls is not open in the same instance of Excel (on the same
machine) when this happens, then the link will not change where it is
looking.
You probably don't have problems in other workbooks because you are not
change where the information is located.
Regards,
Tom Ogilvy
"Paul Sardella" <PSar...@cheerful.com> wrote in message
news:3AC8D32E...@cheerful.com...
Paul Sardella
I think I'm making this harder than it really is; my mind is unclear; my
explanation was poor. There are 1200 hard coded links for
this particular list. I make the changes in Streamer (via a UserForm that calls
a SubRoutine to check that I am not adding a duplicate
symbol). I usually sort Streamer's "database" by symbol. I save Streamer. I then
manually update Bear Bones's links to Streamer. I save Bear Bones. I close Bear
Bones. I re-open Bear Bones - links are okay. After closing Bear Bones multiple
times, I re-open Bear Bones multiple times, saving the file each time - links
are okay. Then, at some unknown future instance in time (probably because the
computer is shut down and restarted) , I re-open Bear Bones - symbols are
incorrect - no changes have been made to Streamer's list during this period.
If it were a case of Bear Bones, upon opening, pointing at the old locations in
Streamer and picking up whatever information is there, I should only see one
instance of each symbol. I am seeing multiple instances of symbols separated by
other symbols, in the correct
alphabetical order . How is Excel getting a copy of all those instances of each
symbol? Seven with ZRAN, three of YHOO, five of
ZQK, etc.
This is an example of this a.m.'s behavior (* indicates a seeming restart in the
order). It occurred after I shut down the machine that I use to view Bear Bones:
Correct InCorrect
WFT ZOMX*
WGRD ZOOX
WHR ZQK
WIND ZRAN
WINK ZQK
WLP ZRAN
WM ITG*?
WMB ZICA
WMI ZIGO
WMT ZIXI
WON ZLC
WPI ZMBA
WTSLA ZOLL
WVCM ZOMX
WWCA ZOOX
XETA ZQK
XICO ZRAN
XIRC ZQK*
XLA ZRAN
XLNX ZOMX*
XOXO ZOOX
XRAY ZQK
XRX ZRAN
XTND XTND*
XTO XTO
YBTVA YBTVA
YHOO YHOO
ZBRA ZBRA
ZICA ZICA
ZIGO ZIGO
ZIXI ZIXI
ZLC ZLC
ZMBA ZMBA
ZOLL ZOLL
ZOMX ZOMX
ZOOX ZOOX
ZQK ZQK
ZRAN ZRAN
Correct is after updating links.
Incorrect is before updating links.
? The symbol ITG seems to be out of place here, because I had purposely saved
Streamer one day without sorting by symbol.
After the computer is shut down, Bear Bones, before updating links, seems to be
"confusing" its links to Workbook Streamer.
Thanks again for your patience in trying to help me to understand this,
Paul
-------------------------------
Tom Ogilvy
your computing environment is redefined and the mapped drives change
(although you showed the link as using a UNC path), causing you to open an
old copy of the file or having the location of the linked to worksheet point
at an old copy of that file. (if there is an old copy of streamer in the
same directory as Bear Bones, this could be a possible source of the
problem). (although you said both files were stored on the came computer -
so assume same location).
Links are evaluated when you open the file and when you do a calculate.
Excel shouldn't change formulas unless both workbooks are open and a change
is made to the structure of the linked to workbook. Sorting in the linked
to workbook does not change the structure and would not alter the formulas
in the linking workbook - so if they pointed to a hard coded cell, they
would display whatever is in that cell. Not sure I can shed anymore light
on it than that without being there.
If you didn't write the code for the useform - then there is a possibility
it is doing something you are not aware of.
Regards,
Tom Ogilvy
"Paul Sardella" <PSar...@cheerful.com> wrote in message
news:3AC912C7...@cheerful.com...
Paul Sardella
I appreciate all the time and effort that you've expended on this issue. I'll
have to let this issue continue to be a mystery for now.
Thanks
Paul
---------------------------
Maarten Meeder
changes.. as the excel does not "remember very well" what has changed.. So
if you do a mass updated of links afer a lot of changes. it may lose some of
the older links..
What was recommend was to perform smaller changes, update links, and
continue.
Hope this give a little light.
Maarten.
"Paul Sardella" <PSar...@cheerful.com> wrote in message
news:3AC4AF9F...@cheerful.com...