Change Excel hyperlinks from absolute to relative

[NatalieK]

I have a folder containing about 2000 wav files and a workbook containing
hyperlinks to these wav files. I backed up this folder several times
without problems, but suddenly managed to change the references. Instead of
the target address showing TY25-08.wav it shows
.../../../../Application%20Data/Microsoft/Excel/TY25-08.wav. I need coding
which will go down a column changing all the hperlinks. The target file name
obviously is at the end of the existing text in the address box, but is also
the Display text for the hyperlink.

 

[Gary Keramidas]

you can try this, just change the worksheet name and the value of the
newpath variable:

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
Dim oldlink As String
Dim newlink As String
Dim fname As Variant
Dim newpath As String

Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
newpath = "\\Application%20Data\Microsoft\Excel\xlstart\"
For i = 1 To lastrow
With ws
oldlink = .Range("A" & i).Value
fname = Split(oldlink, "\")
newlink = newpath & fname(UBound(fname))
.Range("a" & i).Value = newlink
End With
Next
End Sub

 

[NatalieK]

I have now corrected a file with a hundred dudd links by creating this macro
and keying Ctrl+Q, down arrowing 100 times.:-

Sub ResetHyperlink()
'
' ResetHyperlink Macro
'
' Keyboard Shortcut: Ctrl+q
'
Selection.Hyperlinks(1).Address = Selection.Hyperlinks(1).TextToDisplay
End Sub

For the big files, I would like to be able to just select the hyperlinks,
and run a ResetHyperlinks Macro. It is years since I did anything in VBA, so
could you help a damsel in distress and give me the code to loop through the
range.

Thanks