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 he
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.)
Michael wrote:
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