GS wrote:

Terry Pinnell explained on 5/16/2012 :
I am so frustrated! The following problem has occurred before and took me
ages to fix. Now it's happened again out of the blue.

I have a large spreadsheet in Excel 2000 which includes a couple of
hundred hyperlinks. I entered these (over a period of years) in 'absolute'
form, like this example:

C:\Docs\My Videos\PROJECTS\Dovedale 2010 Project\DVD cover
possibles\Front-1.jpg

But today I found that opening one displays the accurate but singularly
unhelpful message 'An unexpected error has occurred'.

And on examining the hyperlinks, I found that they are all now in
'relative' form like this:

..\..\..\My Videos\PROJECTS\Dovedale 2010 Project\DVD cover
possibles\Front-1.jpg

How can that arise? What is triggering Excel to make that unsolicited
change? It wouldn't matter so much if I never moved stuff around, but
that's precisely why I used absolute locations in the first place.

Dave Paterson gave me some advice on this on that previous occasion and
suggested entering a permanent Hyperlink base, such as C:\
I tried it without success and I see that box is now empty.

I've just tried entering C:\Docs and then C:\Docs\ but now on trying to
open a hyperlink I get 'The address of this site is not valid. Check the
address and try again.' Hardly surprising, as the hyperlink itself hasn't
changed and I don't really know how this 'hyperlink base' is getting used.
If it's simply a prefix to all my entries then that would now result in
links like
C:\Docs..\..\..\My Videos\PROJECTS\Dovedale 2010 Project\DVD cover
possibles\Front-1.jpg
which obviously aren't valid.

The central question is, with no base specified and absolute filenames
entered, why does Excel suddenly revert to these relative addresses?

Dave or anyone have any advice on this exasperating problem please?

AFAIK, all 'file' links set in Excel are (locally) relative to where
the workbook is stored on the machine the workbook is running on. When
you move the file to another machine or location the link refs change
from (locally) relative to 'remote refs'. I'm not sure but I'd expect
using the HYPERLINK() function would preserve the link 'as entered'
since it's stored as an 'address string' rather than a physical
hyperlink.

Thanks Garry. So, if from now on I keep the workbook in the same permanent
location, and re-enter absolute locations for my hyperlinks yet again, can
I then rely on that to work permanently?

Note that the files I am linking are in several different folders, across
a couple of drives, if that matters. (It certainly makes it hard to define
a useful 'base'.)

--
Terry, East Grinstead, UK