Hi all, I have discovered a difference of behaviour which I would be
interested in hearing others' views on...

we have a master spreadsheet which contains links to other workbooks,
some in the same location (or subfolders of) x: and some in a different
location altogether, y:.

All is fine as long as the master is not moved... if it is moved, the
links to workbooks in the same area break, but the links to those on x:
are retained. Clearly, if a link is to the same location, then paths
are stored as relative, hence break when you move the workkbook..

However, if I create all the links using UNC paths rather than a drive
mapping then all are retained and work correctly... this is too
complicated for our users though.

My question is, can we change the behaviour of the way links are
stored, so that it retains the full path to linked files rather than
just a relative one? Many thanks for any responses... oh, this is
Office XP, Excel 2002. We are migrating to Office 2003 at end of next
year, does the behaviour change?

oh, my mistake, the unc path was only retained because i had opened the
master from a mapping.... same question applies though, how can i force
a change in behaviour?

I don't believe there is a way to change how links are treated when a
workbook is moved.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

"heathy" wrote in message
oups.com
Hi all, I have discovered a difference of behaviour which I would be
interested in hearing others' views on...

we have a master spreadsheet which contains links to other
workbooks, some in the same location (or subfolders of) x: and some
in a different location altogether, y:.

All is fine as long as the master is not moved... if it is moved,
the links to workbooks in the same area break, but the links to
those on x: are retained. Clearly, if a link is to the same
location, then paths are stored as relative, hence break when you
move the workkbook..

However, if I create all the links using UNC paths rather than a
drive mapping then all are retained and work correctly... this is
too complicated for our users though.

My question is, can we change the behaviour of the way links are
stored, so that it retains the full path to linked files rather than
just a relative one? Many thanks for any responses... oh, this is
Office XP, Excel 2002. We are migrating to Office 2003 at end of
next year, does the behaviour change?


Hi,

Two options that I can think of:

1) Use the INDIRECT function to create the links using full path
names - excel won't change the strings.

That will only work if the source workbooks are open since INDIRECT
cannot see inside a closed workbook.

However, there is an add-in around (Laurent someone wrote it I
believe - apologies for my poor memory) that creates a better version
of INDIRECT (called INDIRECT.EXT) that can see inside closed
workbooks - do a search on INDIRECT.EXE if you are interested in that.


2) Use VBA to re-create all the links each time the workbook is
opened.

That seems like a lot of hard work, and could be quite slow if there
are a lot though.


HTH,

Alan.

--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

Hi,

It was written by Laurent Longre and is he

http://xcell05.free.fr/english/

Scroll down to the MOREFUNC.XLL addin and read about the INDIRECT.EXT
function that it provides he

http://xcell05.free.fr/english/moref...direct.ext.htm

HTH,

Alan.

--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

thanks everyone, will look into those suggestions...