Excel Changes Links Improperly

  • Thread starter Thread starter Jack Cole
  • Start date Start date
J

Jack Cole

We are having a problem where Excel changes links to
other workbooks and breaks them. This comes up in the
following scenario.

Different users have access rights to different
directories and have their drives mapped differently
(i.e. to the directories they have access to). Sometimes
a user modifies data in a file that has links to
directories that don't exist for them. Of course the
links don't work for them, but that's ok. What isn't ok
is that when they save the file, Excel changes the links
(apparently trying to make the links relative to drive
where this user found the destination file) and after
that, the links don't work for anyone, even the original
user that set them up. This happens even though the user
didn't specifically change the links.

We tried converting the links to UNC paths as suggested
by the article KB328440, but ran into a whole different
set of issues.

Is there any other information on relative and absolute
links?
 
UNC paths was going to be my suggestion. I am, therefore, undaunted by your
mention of it causing a different set of issues; rather, I would ask for
specifics about those issues so we can help to solve them. I think that's
probably the best way to go.
--
HTH -

-Frank
Microsoft Excel MVP
Dolphin Technology Corp.
http://vbapro.com
 
We are having a couple problems when make the links UNC
paths.

We have groups of Excel files that we want to update when
machine generated Excel files change. We do this with a
macro that opens all the files and then does a
recalculate. This should update the links where both the
source and destination is file is open. This is MUCH
faster than updating links on File-Open. But sometimes
Excel doesn't recognize that the file it has open is the
one specified in the link. So its link status is "OK"
rather than "Source is open". In this case the links
aren't updated. This is true even though we open the
file using the UNC path, and the link specifies the same
UNC path. Even more frustration is that it doesn't
happen all the time. And often if you fiddle around in
Excel checking things for a few minutes it reverts
to "Source is open".

Another problem is that the UNC links seem to
occasionally change to mapped drive links for no apparent
reason. We have a lot of users, so it is hard to keep
track of exactly who is doing what. But we are pretty
sure nobody is deliberately changing them.

Another problem with UNC links is that you can't easily
have a separate test environment since they are
absolute. Everybody uses the same set of files.
 
Well, I can't say I have a full solution, but some ideas pop to mind:

- Excel has, in its long history, had many situations where there were
problems with recalculation, including at least one that required a patch to
XL97. If you search the KB for the word "recalculate" or "recalculation" in
just the title of the article, you'll get plenty of hits, and some may apply
directly to your situation.

- Having said that, in your VBA code that's opening the individual files,
perhaps forcing a recalculation will help out. (Maybe not until appropriate
patch(es) are installed...)

- What concerns me most about your message is this: "We have a lot of users,
so it is hard to keep track of exactly who is doing what."

Boy, if that isn't the "loaded statement of the week." I don't know what is!

Excel is a great program - arguably the best Windows desktop productivity
program ever. But it should always be kept in mind that it is NOT a database
program, and certainly not a multiple-user database engine/database server.
Anytime you get into "a lot of users" in the same file, you are invariably
going to have problems. In fairness, these problems are, for the most part,
not the fault of Excel but rather of the users.

Excel is designed to give the user complete control over what s/he wants to
do with the file; that's the starting point, and you lock down things from
there. But there are many ways to "break" things accidentally, because there
are many ways to do things and even if a programmer tries to anticipate them
all, it's not so easy to think of all the ("dumb") things a user will try to
do that will break a spreadsheet. (This doesn't even include macro viruses
that may creep up from time to time.) So when you say it's hard to keep
track, I say - EXACTLY. This is like herding cats.

Having said all that, you now look for a solution, I'm sure. One thing you
may be able to try is to move the data that is linked to an external
spreadsheet into a database instead, and then query the data points from a
database engine. Access is one choice, but my personal preference would be
for something more robust, like Microsoft SQL Server or another similar
product. By putting the linked data in a database, you eliminate the need to
have the UNC/mapped drive issue, and you also remove the file-locking issues
associated with sharing that file.

Finally, I'll add this: one of the things that happens most often with Excel
causing problems is that people try to use it as a database program, which
it's not. A database engine is designed to deal with the issues of
record-locking and concurrency, to ensure that people don't step on each
others' data. Excel is only rudimentary in this capability, and even so only
on a file basis, not on a row or record basis. This may or may not apply to
your situation, but it is quite common.
--
HTH -

-Frank
Microsoft Excel MVP
Dolphin Technology Corp.
http://vbapro.com
 
Back
Top