Find/Replace a series of hyperlinks in a workbook

**mellenbal** · 12-03-2014, 03:39 PM

Hi all,

I'm trying to replace the common root of a hyperlink on a series of cells that contain hyperlinks. For example, cell A8 contains a link to c:\myusernumber\myharddrive\shared_folder\mydocument.docx. I need the the hyperlink root to be x:\shared_folder\mydocument.docx. So find "c:\myusernumber
myharddrive" and replace it with "x:"

There are hundreds of links, and the document names are all different.

I'm trying to build a macro to change these, but the macro tool will not allow me to just change the root.

Any help would be truly appreciated!

Thanks in advance!

Mel

**Ron Coderre** · 12-03-2014, 04:25 PM

For future reference...Workbooks have a built-in document property of: Hyperlink Base
When set, all hyperlinks without a server designation begin with the hyperlink base and append the explicit location to it.

Finding it could not be more difficult
• File.Prepare.Properties
...Click: Document Properties...Advanced Properties...Summary tab
...Hyperlink Base -> enter the default hyperlink location here

so if it's set to: c:\myusernumber\myharddrive
your hyperlink would only need to be: \shared_folder\mydocument.docx

When you change the hyperlink base to: x:
your hyperlinks will be directed to that location

Experiment until it behaves the way you want it to.

Is that something you can work with?

**mellenbal** · 12-03-2014, 05:15 PM

Ron, thank you!

I'm not sure I can use the Hyperlink Base as these are all linked to a colleague's local drive, not mine unfortunately. I failed to mention that, so my apologies! I'm also on Excel 2010 if this helps.

Thank you again!

Mel