Hyperlinks change when file is saved

[Grey Geek]

I have inherited an Excel 2003 workbook with 21 tabs in it. It was set up
originally as a stand-alone document to allow users to enter data, save the
file and return it to the originator. Before the file is sent to the user
both the workbook and the worksheets are protected so only specific cells can
be altered. None of these cells contain hyperlinks.

In order to ease navigation within the document and to have a "quick find"
feature for the user, several (dozens actually) hyperlinks were created. For
the most part this all works fine except that, occasionally, a user will
receive the file; open it; navigate and enter their data; rename and save the
file and return it via email to the originator. When the originator opens the
file all of the hyperlinks now refer to the location the user saved the file
in and are therefore considered invalid.

Is there a way to "lock down" the hyperlinks such that they always refer to
the locations within the file only, regardless of the name or location of the
file? If I were not already bald and grey this would surely make me so!

 

[Spiky]

I have inherited an Excel 2003 workbook with 21 tabs in it. It was set up
originally as a stand-alone document to allow users to enter data, save the
file and return it to the originator. Before the file is sent to the user
both the workbook and the worksheets are protected so only specific cells can
be altered. None of these cells contain hyperlinks.

In order to ease navigation within the document and to have a "quick find"
feature for the user, several (dozens actually) hyperlinks were created. For
the most part this all works fine except that, occasionally, a user will
receive the file; open it; navigate and enter their data; rename and save the
file and return it via email to the originator. When the originator opens the
file all of the hyperlinks now refer to the location the user saved the file
in and are therefore considered invalid.

Is there a way to "lock down" the hyperlinks such that they always refer to
the locations within the file only, regardless of the name or location of the
file? If I were not already bald and grey this would surely make me so!

Are you sure these are hyperlinks and not just regular Excel links?

To look up data in a file, it has to be able to find the source file
first. If this is in the same network, it should work if both users
have access to the source file's directory. If the source file is on a
local hard drive, it probably won't work like you want. Make sure all
your source files are on the network.

 

[Grey Geek]

They are identified as hyperlinks and are formated as such. If you mean by
"Excel Links" a formula such as =sheet 2!A1, then no they are not Excel
Links. Is there some other form of link I am missing?

As for being on the network, the files are distributed from a central portal
or via email. The user may then move the file to another machine which is not
on the network, modify it and rename it and then resend it via email to the
originator. Once they are distributed to the user I have no control over how
they are moved before being sent back to the originator.

Based on your response it would seem this will not work properly.
Are there any other suggestions to resolve this issue?

Thank you for taking the time too reply.

 

[Spiky]

They are identified as hyperlinks and are formated as such. If you mean by
"Excel Links" a formula such as =sheet 2!A1, then no they are not Excel
Links. Is there some other form of link I am missing?

As for being on the network, the files are distributed from a central portal
or via email. The user may then move the file to another machine which is not
on the network, modify it and rename it and then resend it via email to the
originator. Once they are distributed to the user I have no control over how
they are moved before being sent back to the originator.

Based on your response it would seem this will not work properly.
Are there any other suggestions to resolve this issue?

Thank you for taking the time too reply.

Sorry, misunderstood you. My fault.

I'm afraid I have little experience with using hyperlinks, so maybe
someone else can help more. But it looks like an internal link should
persist properly. Sounds like yet another neat little bug. Does re-
saving from within Excel fix it?

 

[Grey Geek]

Even when I resave the file on the originators machine, the links do not
update to the new location. It is almost as though somewhere as users make
changes to the file the links become relative to that location as opposed to
being relative to WITHIN the file so that future saves retain the revised
link.
I have discovered that most of the oddities are originating in one specific
portion of the organization. Whether this is a fluke of the Excel
installation there or because the users are doing someting different remains
to be seen. I would have thought (silly me!) that it would be possible to
lock out such changes when the sheets and the workbook are protected.
Thanks again!

 

[Jim Cone]

Here is some info from MS.
However, I think only they understand it...
http://support.microsoft.com/kb/328440/en-us
"Description of link management and storage in Excel"
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Grey Geek" <Grey (e-mail address removed)>
wrote in message
I have inherited an Excel 2003 workbook with 21 tabs in it. It was set up
originally as a stand-alone document to allow users to enter data, save the
file and return it to the originator. Before the file is sent to the user
both the workbook and the worksheets are protected so only specific cells can
be altered. None of these cells contain hyperlinks.

In order to ease navigation within the document and to have a "quick find"
feature for the user, several (dozens actually) hyperlinks were created. For
the most part this all works fine except that, occasionally, a user will
receive the file; open it; navigate and enter their data; rename and save the
file and return it via email to the originator. When the originator opens the
file all of the hyperlinks now refer to the location the user saved the file
in and are therefore considered invalid.

Is there a way to "lock down" the hyperlinks such that they always refer to
the locations within the file only, regardless of the name or location of the
file? If I were not already bald and grey this would surely make me so!

 

[Whirly]

Did you ever figure this out? You are not alone, my friend, which makes me
feel better about myself because I am NOT going crazy...except in trying to
figure these things out. I have hyperlinks to external documents, which are
specified using the full path name to a shared server. When I save the
document, the beginning of the hyperlink is truncated and replaced by
/.../.../.../.

 

[Whirly]

Thanks, Jim. That helped me understand WHY it was happening, now if only I
could figure out a workaround!