Code for hyperlink advice

[ipbr21054]

Morning,
On my worksheet i have many invoices which in column P is there invoice number which is hyperlinked to a folder on my pc.
Today clicking on one of the hyperlinked numbers shows a message Unable to open hyperlink.

When i select edit hyperlink i see in the Address field something like the following.
../../../AppData/AppData/AppData/Roaming/Microsoft/DR%20COPY%20INVOICES/80.pdf

If i delete it & save it as a hyperlink again i now see the following.
DR%20COPY%20INVOICES/80.pdf

This works fine.

This is i have a couple of hundred to do.

Is there some code etc that will just show in the address field the part
DR%20COPY%20INVOICES/80.pdf

In this case the invoice is 80 but apart from that the rest of the cose is the same.

Otherwise im going to be here ages.
I dont see why they are even broken ???

Thanks

 

[Trevor G]

Try this. Use Find and Replace (Ctrl + H). Edit one of the other hyperlinks which has the wrong address and copy the part that isn't correct and replace it with the correct part and select Replace All, it shouldn't change your invoice numbers at all.

 

[ipbr21054]

Thanks but it just keeps saying cant find a match

 

[Trevor G]

Follow the instructions from here.

How to Quickly Find Hyperlinks in Excel (using Find and Replace)

In this tutorial, you'll learn how to use Find and Replace to quickly find hyperlinks in Excel. You can find all the hyperlinks or the ones that contain a specific text.

trumpexcel.com

 

[Trevor G]

Try this code

Sub ChangeHyperlinks()
Dim OStr As String, NStr As String
Dim hyper As Hyperlink

OStr = "Old path" 'Change the path name
NStr = "New path" 'Change to the new path

For Each hyper In ActiveSheet.Hyperlinks
    
    If InStr(1, hyper.Address, "\") > 0 Then
        hyper.Address = Replace(hyper.Address, OldStr, NewStr)
    Else
        hyper.Address = NewStr & "\" & hyper.Address
    End If
    'If you want the text for display to change remove the apostrophe from the next line
    'hyper.TextToDisplay = Replace(hyper.Address, OStr, NStr)

Next hyper

End Sub

 

[ipbr21054]

Thanks.
Post #4 worked for me.

Many thanks

 

[Trevor G]

Good to read you have a solution. Thank you for letting me know.