Andreas Hermle
asked on
Change all hyperlink addresses (Network paths) on the active worksheet using VBA
Dear Experts:
below macro adds the substring _bw at the end of every hyperlink address such as ...
C:\test\MyProduct.pdf (Before)
C:\test\MyProduct_bw.pdf (After)
Could somebody please rewrite / tweak the code so that the other way round is also possible, i.e. I would like to delete the substring _bw from the end of every hyperlink address.
Help is much appreciated. Thank you very much in advance.
Regards, Andreas
below macro adds the substring _bw at the end of every hyperlink address such as ...
C:\test\MyProduct.pdf (Before)
C:\test\MyProduct_bw.pdf (After)
Could somebody please rewrite / tweak the code so that the other way round is also possible, i.e. I would like to delete the substring _bw from the end of every hyperlink address.
Help is much appreciated. Thank you very much in advance.
Regards, Andreas
Sub UpdateHyperLink()
Dim hLink As Hyperlink
Dim wSheet As Worksheet
Dim Link, StartNum, EndNum, StrFound
Set wSheet = ActiveWorkbook.ActiveSheet
wSheet.Activate
For Each hLink In wSheet.Hyperlinks
Link = hLink.Address
Link = Replace(Link, Right(Link, 4), "_bw" & StrFound & Right(Link, 4))
hLink.Address = Link
Next hLink
End Sub
Function FindN(sFindWhat As String, sInputString As String, N As Integer) As Integer
Dim J As Integer
Application.Volatile
FindN = 0
For J = 1 To N
FindN = InStr(FindN + 1, sInputString, sFindWhat)
If FindN = 0 Then Exit For
Next
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.