Refreshing hyperlinks on backup copies

[L33]

Hi,

I have a workbook with hyperlinks to several other workbooks that are stored in a number of different folders. I want to be able to copy the whole root folder in which they are all contained on to a shared drive, but maintain the hyperlink structure. Although I'm maintaining the folder hierarchy, the hyperlinks still point at the original versions of the folders on my C: drive.

Does anyone have a macro that I can run on the master workbook that'll go looking for the other files and update the links?


Thanks!

 

[nirvana_]

Make sure you have a backup of your files before trying this. Just a few things to remember.

1) In this case the file is assumed to be stored locally in "C:\temp\"

Hence I have used Left(h.address,8) which mean "C:\temp\" is 8 chars in length.

If for example you use the local address as "C:\My Temp\" you will have to re-modify the statement to Left(h.address,11) since "C:\My Temp\" is 11 chars in length.

2) The new mid statement will 8+1 in case of "c:\temp\" which means.
h.Address = NewLocation & Mid(h.Address, 9, Len(h.Address))

The new mid statement will 11+1 in case of "c:\temp\" which means.
h.Address = NewLocation & Mid(h.Address, 12, Len(h.Address))

3) This code only changes the hyperlinks not the display actual names within the excel sheet.

You will need to modify this code according to your needs.

Hope this makes sense.

Sub UpdateHyperlinks()
    Dim NewLocation As String
    NewLocation = "Z:\NewLocation\"
    For Each h In Worksheets(1).Hyperlinks
        If Left(h.Address, 8) = "c:\temp\" Then
            h.Address = NewLocation & Mid(h.Address, 9, Len(h.Address))
        End If
    Next
End Sub