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.

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
--


Gary

"NatalieK" <(E-Mail Removed)> wrote in message
news:9B549853-B112-4BC0-84C3-(E-Mail Removed)...
>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.

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

"Gary Keramidas" wrote:

> 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
> --
>
>
> Gary
>
> "NatalieK" <(E-Mail Removed)> wrote in message
> news:9B549853-B112-4BC0-84C3-(E-Mail Removed)...
> >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.
>
>
>