Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing multiple hyperlinks
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing multiple hyperlinks
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Hyperlinks in Macros | Excel Discussion (Misc queries) | |||
Hyperlinks keep changing | Excel Discussion (Misc queries) | |||
How do I prevent Hyperlinks from changing when saved? | Excel Discussion (Misc queries) | |||
Help: Excel hyperlinks changing... | Excel Discussion (Misc queries) | |||
Changing Hyperlinks? | Excel Discussion (Misc queries) |