Updating all hyperlinks in a linked spreadsheet

[henryJT]

I'll start by saying, I'm not good at all with VBA - but if you have easy to follow instructions that would be great

A user of mine, has a spreadsheet with around 300 linked documents in a spreadsheet. The problem being, the documents are either on their own desktop, or a couple of other peoples. Moving to a full Win10 environment this weekend, all those links will break, since, new profiles, new desktop locations etc.
Is there a way to update all the links that will be
\\fileserver\profiles\username\desktop\folder\filename.docx

to

H:\folder\filename.docx

I was hoping I could update them all at once, copy the files to the new location and everything will be great. This is a very important document for the users and the thought of manually updating all the links when I'm already got virtually no spare time brings me to tears. Any help would be greatly appreciated!

 

[John_w]

Try running this macro on a copy of your workbook:

Public Sub ChangeHyperlinks()
    Dim ws As Worksheet
    Dim hlink As Hyperlink
    Dim p As Long
    For Each ws In ActiveWorkbook.Worksheets
        For Each hlink In ws.Hyperlinks
            p = InStrRev(hlink.Address, "\")
            If p > 0 Then
                hlink.Address = "H:\folder\" & Mid(hlink.Address, p + 1)
            End If
        Next
    Next
End Sub