Changing multiple hyperlinks

[Michael]

Hi, I have a spreadsheet with a list of about 100 hyperlinks to files I use
and, because some of our folders have been moved and the folder names have
changed, the links now no longer work.

I have tried using edit/replace to change the links but that is only
changing the name of the hyperlink not the actual link itself. Does anyone
know of a way of changing the links for all of the files at all please?

All of the links have the same root for example

The files

U:\CommercialServices\book1
U:\CommercialServices\book2
U:\CommercialServices\book3
U:\CommercialServices\book4
U:\CommercialServices\book5

Are now all called

U:\ LCCG\Business Support & Services\Fin & ICT\Commercial Finance\book1
U:\ LCCG\Business Support & Services\Fin & ICT\Commercial Finance\book2
U:\ LCCG\Business Support & Services\Fin & ICT\Commercial Finance\book3
U:\ LCCG\Business Support & Services\Fin & ICT\Commercial Finance\book4
U:\ LCCG\Business Support & Services\Fin & ICT\Commercial Finance\book5

 

[Dave Peterson]

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