Weird corruption of hyperlinks

[Terry Pinnell]

I'm using Excel 2000. Somehow the hyperlinks I've created, which used to
be OK, have now become oddly 'corrupted'. Take an example, an entry for
which the text is fine:
Zakopane2002.mpg

If I use Hyperlink > Edit Hyperlink I see this in the filename:
Intro Title Projects\Intro Clips (MPGs)\Zakopane2002.mpg

If I close that and then click the hyperlink, in the usual message about
viruses I see:
Opening C:\Docs\Touch_Pro2 My Documents\MyTP2Misc\Intro Title
Projects\Intro Clips (MPGs)\Zakopane2002.mpg

That string 'Touch_Pro2 My Documents\MyTP2Misc' is what is bizarre. I do
have a folder of that name, but it has no relevance here! I see the same
incorrect filename if I hover over the hyperlink.

So, as it looks wrong anyway, I changed the filename explicitly to the
correct location:
C:\Docs\My Videos\PROJECTS\Intro Title Projects\Intro Clips
(MPGs)\Zakopane2002.mpg

But clicking that gives the same strange result!

Anyone have any idea what might be happening here please?

 

[Dave Peterson]

Saved from a previous post:

A few people have said that this has stopped a similar problem from occurring.
Maybe it'll help you--but you'll have to test it to make sure.

In xl2003 menus:
File|Properties|Summary Tab|Hyperlink Base
change it to C:\
(something that's always available)

In xl2007:
Click on the Office button
Choose Prepare, then properties.
Then use the arrow on the Document Properties dropdown
Choose Advanced Properties
And then change the hyperlink base on the Summary tab.


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.

========

If you used Insert|Hyperlink, then you've noticed that edit|replace won't touch
those hyperlink addresses.

If you used Insert|hyperlink (xl2003 menus), you'll have more work to do. But
the good news is David McRitchie has done most of it for you:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm
look for:
Fix Hyperlinks (#FixHyperlinks)

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Terry Pinnell]

Saved from a previous post:

A few people have said that this has stopped a similar problem from occurring.
Maybe it'll help you--but you'll have to test it to make sure.

In xl2003 menus:
File|Properties|Summary Tab|Hyperlink Base
change it to C:\
(something that's always available)

In xl2007:
Click on the Office button
Choose Prepare, then properties.
Then use the arrow on the Document Properties dropdown
Choose Advanced Properties
And then change the hyperlink base on the Summary tab.

Thanks Dave, appreciate the help.

I've just tried the above but unfortunately it didn't work. I changed the
base to C:\

Then, with a clean cell, I browsed to
C:\Docs\My Videos\Intro MPGs\Zakopane2002.mpg

The Hyperlink dialog box filename was now
Docs\My Videos\Intro MPGs\Zakopane2002.mpg
which looks correct.

But opening it still gives the error "An unexpected error has occurred."

And all OTHER previously working links now fail, with the message "The
address of this site is not valid. Check the address and try again." I
thought that simply clearing the base again would fix that, but it hasn't
;-(

I'll take a deep breath and a coffee and then tackle it further!

 

[Terry Pinnell]

Thanks Dave, appreciate the help.

I've just tried the above but unfortunately it didn't work. I changed the
base to C:\

Then, with a clean cell, I browsed to
C:\Docs\My Videos\Intro MPGs\Zakopane2002.mpg

The Hyperlink dialog box filename was now
Docs\My Videos\Intro MPGs\Zakopane2002.mpg
which looks correct.

But opening it still gives the error "An unexpected error has occurred."

And all OTHER previously working links now fail, with the message "The
address of this site is not valid. Check the address and try again." I
thought that simply clearing the base again would fix that, but it hasn't
;-(

I'll take a deep breath and a coffee and then tackle it further!

Here's another factor that is muddying the water. The program I had
associated with MPG files was the VLC player. That has always worked fine
when the MPG was d-clicked from Explorer windows, and I'm pretty sure
previously it worked OK from these Excel 2000 hyperlinks too. But as one
step to isolating my problem I've now just changed the association to
Media Player Classic (MPC) instead. That now allowed me to successfully
open a correctly specified hyperlink, while it had failed previously with
"An unexpected error has occurred."

I still have the major issue to resolve, but at least that's one barrier
removed.

 

[Dave Peterson]

I'd double check that hyperlink base. I've never seen excel behave that way and
changing the hyperlink base has worked for lots of posters.

And after you changed the hyperlink base, did you change the hyperlinks back to
what they were supposed to be. That was the purpose of the link to David
McRitchie's site.

 

[Terry Pinnell]

I'd double check that hyperlink base. I've never seen excel behave that way and
changing the hyperlink base has worked for lots of posters.

And after you changed the hyperlink base, did you change the hyperlinks back to
what they were supposed to be. That was the purpose of the link to David
McRitchie's site.

Thanks Dave, appreciate your sticking with this. I'm taking another hard
look at it.

One aspect that I'm finding hard to grasp (haven't touched this stuff for
ages) is the syntax itself. For example, I enter the full location:
C:\Docs\SUNDRY\CD+DVD\CD-DVD Inserts\Anthony39\Front4.jpg

If I then save the spreadsheet (which, in case it's relevant, is at
C:\Docs\Touch_Pro2 My Documents\MyTP2Misc\DVD-Index.xls)
re-open it, and use Edit Hyperlink again, Excel 2000 now shows it as
...\..\SUNDRY\CD+DVD\CD-DVD Inserts\Anthony39\Front4.jpg

Why is that please?

 

[Terry Pinnell]

Thanks Dave, appreciate your sticking with this. I'm taking another hard
look at it.

One aspect that I'm finding hard to grasp (haven't touched this stuff for
ages) is the syntax itself. For example, I enter the full location:
C:\Docs\SUNDRY\CD+DVD\CD-DVD Inserts\Anthony39\Front4.jpg

If I then save the spreadsheet (which, in case it's relevant, is at
C:\Docs\Touch_Pro2 My Documents\MyTP2Misc\DVD-Index.xls)
re-open it, and use Edit Hyperlink again, Excel 2000 now shows it as
..\..\SUNDRY\CD+DVD\CD-DVD Inserts\Anthony39\Front4.jpg

Why is that please?

On this page
http://office.microsoft.com/en-us/ex...005202555.aspx
I read:
"You can override the hyperlink base address by using the full, or
absolute, address for the hyperlink in the Insert Hyperlink dialog box."

So when I entered the full address 'C:\Docs\My
Videos\PROJECTS\Flight-Simon-14Aug2011\20110814FlightSimon-2.mp4'
it seems I was right to expect that to work. Despite the inclusion of a
space (My Videos), the addition of quotes was apparently not only
unnecessary but scrambled the whole thing. And presumably that's
regardless of whether there's a base entered in the Properties or not.

However, I'm coming to the conclusion that all that is irrelevant and that
this is an Excel 2000 limitation. I converted a couple of MP4s to MPGs and
the hyperlinks to those worked. I'd still like to hear from anyone else
with Excel 2000 though, to be 100% sure of this!

 

[Dave Peterson]

I haven't used xl2k in a longgggggggggg time.

But I have seen the hyperlink base technique work many times.

I don't have a guess why you're having trouble.