Hyperlinks changed by Excel again!

[Terry Pinnell]

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?

 

[GS]

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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Terry Pinnell]

Terry Pinnell explained on 5/16/2012 :

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'.)

 

[GS]

Terry Pinnell explained :

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?

Nope! Won't work too well if you move the files elsewhere. (Using

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'.)

I suggest you use a different method of tracking. Perhaps a file
listing utility that also stores the folder path somewhere on the sheet
the files are listed on. Concludes using 1 sheet per folder path. The
utility can include some right-click menus that let you process
selected files in the list however you want.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Terry Pinnell]

Terry Pinnell explained :

Nope! Won't work too well if you move the files elsewhere. (Using
absolute locations only works when you don't move anything around<g>!)

Yes, I'm assuming the linked files remain in their current locations. I'm
sure that has also been the case since the *last* time I entered their
absolute addresses in the spreadsheet. So the only cause I can think of
for the loss of those is that I must have moved the workbook itself.

One other possibility is that it's something to do with the fact that this
workbook is one of those regularly synced automatically between my PC and
my smartphone (a Windows Mobile based HTC Touch Pro 2).

I suggest you use a different method of tracking. Perhaps a file
listing utility that also stores the folder path somewhere on the sheet
the files are listed on. Concludes using 1 sheet per folder path. The
utility can include some right-click menus that let you process
selected files in the list however you want.

I'll persist with hyperlinks for the moment, as it's so simple - when it
works! The workbook is a record of the DVDs I've made, so the hyperlinks
let me immediately display
- the video
- the case covers
- the DVD menu
etc.

Last August Dave Peterson also suggested the following. It looked a bit
heavy going for my rusty skills but I may get stuck in and attempt it if
the links revert again.
--- Start paste ---
I like to use the =hyperlink() worksheet function for this. I've never
had one
of these worksheet functions change.

I'll put the path in a hidden cell (A1 in my example). (Include the
trailing
backslash, too!)

Then put the filenames in A2:Axxx
Then use the =hyperlink() formula in B2:Bxx.

=hyperlink("File:////"&$a$1&a2,"Click me")
and drag down

Then when I need to change the folder, I can change it one location (A1).

I could embed the path directly in the formula:
=hyperlink("File:////c:\my documents\excel\"& a2,"Click me")
and use Edit|Replace, but that seems like more work to me.
--- End paste ---

Meanwhile I've just re-entered 3 columns of absolute hyperlinks again,
some 600 entries altogether, after first developing 3 Macro Express macros
to do the grunt work.

 

[GS]

Terri,
I agree with Dave's approach si nce it makes for easier ongoing
maintenance. My reason for suggesting using the HYPERLINK() function is
on par with Dave's assessment!

Otherwise, I like the file listing utility approach because I have both
an Excel addin and a VB6.exe designed for this. Both allow me to filter
for filetypes and display other file metadata I choose to display. It
also opens the files for viewing/editing in my specified viewer/editor
app. While both these apps are specially designed to explicitly manage
CNC machine program files, they could be easily configured to work with
any filetypes specified in the Options>FileTypes list.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion