Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How Can I Globally Change Hyperlink Addresses?
Excel 2003, XP Pro I have a single-sheet workbook with 600 and climbing entries in col A, each with a hyperlink. The files in each link are now located on an external drive so that I can work on building the sheet from different computers. Eventually, all the files will be moved to a more permanent location on a different computer, exact address unknown. My question is: how does one change the addresses of each hyperlink in a global fashion? I really don't want to stroke in new addreses! Present Address: file:///E:\FOLDER E1\TEMP QSL DATA\Data Filename Tentative Final Address: file:\\\C:\FINAL QSL DATA\Data Filename I am not fluent in VBA, and I know just enough about Excel to be dangerous! I would truly appreciate any solutions, especially in the hand-holding department! Cheers-- Terry--WB4FXD Edenton, NC |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How Can I Globally Change Hyperlink Addresses?
I like to use the =hyperlink() worksheet function for this.
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. Saved from a previous post: 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.) Terry wrote: Excel 2003, XP Pro I have a single-sheet workbook with 600 and climbing entries in col A, each with a hyperlink. The files in each link are now located on an external drive so that I can work on building the sheet from different computers. Eventually, all the files will be moved to a more permanent location on a different computer, exact address unknown. My question is: how does one change the addresses of each hyperlink in a global fashion? I really don't want to stroke in new addreses! Present Address: file:///E:\FOLDER E1\TEMP QSL DATA\Data Filename Tentative Final Address: file:\\\C:\FINAL QSL DATA\Data Filename I am not fluent in VBA, and I know just enough about Excel to be dangerous! I would truly appreciate any solutions, especially in the hand-holding department! Cheers-- Terry--WB4FXD Edenton, NC -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How Can I Globally Change Hyperlinks? | Links and Linking in Excel | |||
How do I make a global change of Hyperlink addresses? | Excel Discussion (Misc queries) | |||
How do I change the font globally for all comments? | Setting up and Configuration of Excel | |||
change values globally | Excel Worksheet Functions | |||
How can I globally change the formatting of all my comments in Exc | Excel Discussion (Misc queries) |