Excel hyperlink problem

[deva]

Hello everyone,

I have a strange excel problem and it's driving me nuts - I would be
seriously grateful if someone could help me out!

A colleague has this excel document with a huge number of hyperlinks in
it, well over 1000 (don't ask me why she wants to use excel - she just
does). The hyperlinks are in this format and are using DFS:

\\cc\root\pte\tru\ch\scandata\Archaeology\1980\xxx.pdf

Whenever she clicks on a link, a pdf from another area of the network
is opened. Quite simple and no problems so far. However, as of
yesterday, whenver she makes any kind of change to the excel file -
even adding a single number to a cell - and then saves it again, all
the hyperlinks become corrupted to this format:

../../../scandata/Archaeology/1980/xxx.pdf

I've tried everything I can think of, but it still corrupts all the
time.

Has anyone seen a problem like this before? Would be very grateful for
any assistance anyone can provide me with! Thanks in advance..

D.

 

[Richard]

Hi

No answer I'm afraid, but I have seen it before. Just
simple links between spreadsheets sometimes do this. My
previous answers were that it is a network issue and not
an Excel issue, but I never found a satisfactory.

Not much help I know, but at least you know it's not just
you.

 

[Dave Peterson]

I've never personally seen it, but I've seen posts describing this problem.

I think it has something to do the common directory structure between the
workbook and the hyperlinks.

Something I've never used...

Maybe you could test to see if this helps.
File|Properties|Summary Tab
there's a hyperlink Base field that you could use to specify the base address.

(I have no idea if that'll help or not, though.)

===
But I usually use the worksheet formula:
=hyperlink()
I've never seen that get adjusted (or heard of that being altered, either).

One way to extract those URL's from a hyperlink created via Insert|Hyperlink
is with a userdefinedfunction.

Here's one that may help:

Option Explicit
Function GetURL(Rng As Range) As String
Application.Volatile

Set Rng = Rng(1)

If Rng.Hyperlinks.Count = 0 Then
GetURL = ""
Else
GetURL = Rng.Hyperlinks(1).Address
End If
End Function

So if you had a hyperlink in A1, you could put =getURL(a1) in that adjacent
cell.

Then convert those formulas to values. Then use =hyperlink(b1) instead.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Spork Rhonewood]

I had the same problem and here's my solution. It apprears that whe
ever you save an excel speradsheet to a network drive it changes al
the hyperlink references from absolute ones :

\cc\root\pte\tru\ch\scandata\Archaeology\1980\xxx.pdf

to relative ones:

../../../scandata/Archaeology/1980/xxx.pdf.

The only way I could find to get around this was to open the options.
Tools->Options->General->Web Options->Files. Once there uncheck th
box next to: "Update links on save."

That solved the problem. Like I said, excel appears to only change th
references when the files is saved to a nework drive. Oh and I di
this on 2002 SP-2. Hope this helps.
-Spor