EXCEL Hyperlinks go from absolute to relative 1

[scon44]

Ok so my user suddenly has absolute hyperlinks to documents that once were relative. In other words the hyperlinks at one point in the past were pointing relative to other documents in the user's network drive now the hyperlinks go to c:\docs and setting \etc\etc.

I had him setup to use off line files and folders so I'm assuming that messed him up somehow.

Any ideas on to go into the current excel document and then do a setting change to fix all these at once.... there are hundreds of links.
Thanks

 

[PeterMoran]

Hi,

If you can do it via a Search and Replace (NB the default Search and replace in Excel will not do this!), here is some code I have used which should do the trick, thanks to Jan Karel Pieterse and the WOPR Forum

Option Explicit
Sub ReplaceInHyperlinks()
‘   Thanks to Jan Karel Pieterse
Dim oLink As Hyperlink
Dim sFind As String
Dim sReplace As String
    sFind = InputBox("Please enter the text to search for", "Find and Replace in Hyperlinks")
    If sFind = "" Then Exit Sub
    sReplace = InputBox("Please enter the text to replace with", "Find and Replace in Hyperlinks")
    If sReplace = "" Then
        If MsgBox("No replace text was entered, continue replacing with nothing?" _
        , vbYesNo, "Find and Replace in Hyperlinks") = vbNo Then Exit Sub
    End If
    For Each oLink In ActiveSheet.Hyperlinks
        If InStr(oLink.Address, sFind) > 0 Then
            oLink.Address = Application.WorksheetFunction.Substitute(oLink.Address, sFind, sReplace)
        End If
    Next
End Sub

Good Luck

Peter Moran