Global Change of Hyperlinks in Excel

[Paige Miller]

Suppose I have an Excel spreadsheet where all the hyperlinks point to
a server, for example

\\sn230aa\documents

and I want to globally change all the hyperlinks to

\\zz189\documents

Can I do this in Excel?

 

[Dave Peterson]

Are these hyperlinks that you've added via Insert|Hyperlink?

Take a look at David McRitchie's site:
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

 

[Klemen25]

Can't you just select all tabs, edit- replace, find \\sn230aa
\documents, replace with: \\zz189\documents.

Should work.

Klemen

 

[Dave Peterson]

Ps.

I like to use the =hyperlink() worksheet function:

With the filename in A2:
=hyperlink("file:////"&"\\sn230aa\documents\" & a2,"Click me"
(check the syntax--I'm not sure if I messed it up).

Then a simple edit|replace will fix the problem

In fact, if you put the path in A1 (and hide that row???):
A1: \\sn230aa\documents\
Then use this in B2:
=hyperlink("file:////" & $a$1 & a2,"Click me"
And then I only have to change A1 and all the hyperlinks that use that formula
are fixed.