Help with mass Edit of Hyperlinks

[Reimer]

Hi,
A coworker is having a problem with Hyperlinks. There is an Excel 2003 workbook saved on our Intranet. It has many hundreds of hyperlinks to Excel Workbooks that are stored in subfolders below the main workbook.
When he opens the main workbook in Excel 2007 (we just got upgraded to it), he could not get any of the hyperlinks to work. I looked at it and it looks like all of the hyperlinks have a section of the path that is duplicated.
By this I mean the link contains one part of the path twice so the links do not work. No idea how this happened but we need to fix it. Is there code that can do a search and replace on the entire workbooks hyperlinks?
example Search all hyperlinks and Replace: \01_Interceptors\01_interceptors\ with: \01_interceptors\

I would appreciate if someone could point me in the right direction.

Thanks for any and all input

[HansV]

Try this:

Code: Select all

Sub UpdateHyperlinks()
  Const strFind = "\01_Interceptors\01_interceptors\"
  Const strReplace = "\01_Interceptors\"
  Dim wsh As Worksheet
  Dim hyp As Hyperlink
  For Each wsh In ActiveWorkbook.Worksheets
    For Each hyp In wsh.Hyperlinks
      hyp.Address = Replace(hyp.Address, strFind, strReplace)
      hyp.TextToDisplay = Replace(hyp.TextToDisplay, strFind, strReplace)
    Next hyp
  Next wsh
End Sub

[Reimer]

Hans,

YOU DA MAN!

Thanks I will give it a try and post back.

Thanks again

[Reimer]

Hans,
Sorry for the Delay -It turns out my co-worker is not coming in today, and I do not have access to that section of the Intranet.

[HansV]

No problem!

[Reimer]

Hans,

He was in today! It failed the first time, so we saved the file to his desktop and ran the macro against that file -It worked beautifully (of course).
Thank you soooo much. There looked to be over 1,000 hyperlinks in total. Some of them had a different problem with the path so we modifed the macro and ran it again.
We got them ALL.

Once again I owe you many thanks! I just wish some day I could return the gift.
Have a Great Day.

[HansV]

I'm glad you were able to modify the macro as needed!

[Reimer]

Hans,

It was so easy to understand (and modify) that I was unhappy I could not figure it out for myself (I did try).
You do write beautiful code.