Can i change multiple hyperlinks at once

[Guest]

I have a large database of files that are hyperlinked in a File Register
created in excel. All of these files have now been moved to a different
drive, but the folder names etc are the same. Is there a quick way to change
the path of the hyperlink, as to do each one individually would take weeks.

 

[Mladen_Dj]

I have a large database of files that are hyperlinked in a File Register
created in excel. All of these files have now been moved to a different
drive, but the folder names etc are the same. Is there a quick way to
change
the path of the hyperlink, as to do each one individually would take
weeks.

Just go to Edit->Replace, and choose part of path which was changed (for
example C:\), and write it in "Find what:" box, than put new patcial path in
"Replace with" box (for example D:\). unselect "Match entire cell
contents:" if it is selected, and click "Replace all".

 

[vezerid]

The following VBA function will replace the drive names as you desire
after you change the words inside the quotes to the desired ones.

Sub ChangeHyperlinks()
Dim h As Hyperlink
Dim oldDr As String, newDr As String

oldDr = "http"
newDr = "ftp"
For Each h In Sheets("Sheet1").Hyperlinks
h.Address = newDr & Mid(h.Address, Len(oldDr) + 1, Len(h.Address))
Next h
End Sub

HTH
Kostis Vezerides

 

[Mladen_Dj]

Just go to Edit->Replace, and choose part of path which was changed (for
example C:\), and write it in "Find what:" box, than put new patcial path
in "Replace with" box (for example D:\). unselect "Match entire cell
contents:" if it is selected, and click "Replace all".

Huh, its seems not work , because excel don't change path but only text in
the cell. Then you can use formula, for example if hyperlinks are in column
A, put in B1 "=HYPERLINK("D"&RIGHT(A1,LEN(A1)-1))" where D is drive letter
which is changed, and then copy formula to the end of file list. Sorry for
the previous wrong tip.