I have a series of spreadsheets with hyperlinks to PDF files. I am changing the directory of the PDFs and want to update a portion of the hyperlinks from "\\SharedDrive\PublicFolder\AnotherFolderA\AnotherFolderA\2011\CC-11-280.pdf" to "\\SharedDrive\PublicFolder\AnotherFolderB\AnotherFolderB\2011\CC-11-280.pdf"
There is a thread (called Macro to replace part of hyperlink) which had the following VBA code:
Dim lnk As String, rpl As String
Dim i As Integer
rpl = Selection.Value
For i = 3 To 5
lnk = Selection.Offset(i, 0).Value
lnk = Mid(lnk, 1, InStr(1, lnk, "/")) & rpl & Right(lnk, Len(lnk) - InStrRev(lnk, ".html") + 1)
Selection.Offset(i, 0).FormulaR1C1 = lnk
I modified it and ran it but it also add the updated hyperlink text to the existing displayed text. (CC-11-280 became AnotherFolderB\AnotherFolderBCC-11-280)
I need to update all of the links in a column where some of the links have been updated and several have not.