Hyperlink Query

  • Thread starter Thread starter John H
  • Start date Start date
J

John H

Hi,

We have this spreadsheet which resides on a network share (M drive),
within this spreadsheet there are numerous rows of data, each one
refers to the M drive, via a hyper-link this then opens another
spreadsheet on the M drive within a seperate folder. The problem I
have is, somehow all of the link locations have changed to C:
\documents and settings\<user>\application data\microsoft\excel
\<folder name>\<folder number> (20,000 of them !)
I suspect that someone has copied the file of the network, worked on
it locally and saved it to there C:, then copied the file back up. Can
someone please shed any light on this as to either, change all the
links back to the correct location or suggest how this may have
happend so that I can stop it happening again.

As always, your help is much appricated.

John.
 
The first thing I would do is convert the insert|hyperlink style hyperlinks to
=hyperlink() worksheet functions. I've never had any trouble with them.

If that's not possible, I would try is to use the UNC Path instead of the mapped
drive.

David McRitchie has some code that you can use to re-point them to that UNC
location.

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

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

And to stop it from happening again...

A few people have said that this has resolved the problem for them:

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


==========

The way I use the =hyperlink() function.

I'd put the UNC path in a cell (like A1) and then the file names in A2:Axxx (or
whatever).

Then in B2, I'd put this formula:
=HYPERLINK("File:////" & $a$1 & a2, "Click Me")

(A1 has to end with a backslash: \\server\share\folder1\folder2\

========

If you want to try to retrieve the hyperlink addresses out of the
insert|hyperlink style, you can use a User defined function to retrieve the
link.

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 you could convert to values (edit|copy, edit|paste special|values), you
could use edit|replace to correct the location (change C:\ to M:\, or use the
UNC path).

Then delete the original cells with the links and use the =hyperlink() formulas.
 
Back
Top