global hyperlink modification?

[UncleBose]

Hello

I use an Excel 2003 sheet with about 400 hyperlinks which point to web archive files (.mht) saved in a nearby folder. On the sheet, these links are denoted by a clickable "x" rather than the entire path of the .mht. Excel crashed recently, and when I saved an auto-recovered copy, I found that all the hyperlinks were changed, rendering them useless. I get a message saying "Cannot open the specified file".

The original path was file:///C:\folder1\folder2\folder3\folder4\FOLDER5\name of .mht

The modified path is: file:///C:\Documents and Settings\owner\Application Data\Microsoft\Excel\FOLDER5\name of .mht.

"FOLDER5," and of course all the .mhts, do not exist in the modified path. In the original path, I had "folder4" name "Excel". Did this confuse the auto-recovery program, causing it to point to the wrong Excel folder?

The thought of manually fixing all 400 links is unpleasant. Is there a way to globally modify all these hyperlinks back to their original state?

Thanks for any help.

 

[nirvana_]

Make sure you have a backup of your files before trying this. Just a few things to remember.

1) In this case the file is assumed to be stored locally in "C:\temp\"

Hence I have used Left(h.address,8) which mean "C:\temp\" is 8 chars in length.

If for example you use the local address as "C:\My Temp\" you will have to re-modify the statement to Left(h.address,11) since "C:\My Temp\" is 11 chars in length.

2) The new mid statement will 8+1 in case of "c:\temp\" which means.
h.Address = NewLocation & Mid(h.Address, 9, Len(h.Address))

The new mid statement will 11+1 in case of "c:\temp\" which means.
h.Address = NewLocation & Mid(h.Address, 12, Len(h.Address))

3) This code only changes the hyperlinks not the display actual names within the excel sheet.

You will need to modify this code according to your needs.

Hope this makes sense.

Sub UpdateHyperlinks()
    Dim NewLocation As String
    NewLocation = "Z:\NewLocation\"
    For Each h In Worksheets(1).Hyperlinks
        If Left(h.Address, 8) = "c:\temp\" Then
            h.Address = NewLocation & Mid(h.Address, 9, Len(h.Address))
        End If
    Next
End Sub