Editing Intra-Network Hyperlinks in a Spreadsheet

[Winston]

We have an Excel spreadsheet with several hundred
hyperlinks to various files on one pc in our network. When
we access this spreadsheet in its normal location, the
hyperlinks work beautifully.

However, on the rare occasion that that pc is down, even
though we have a (daily) backup of all these files that we
can save on some other pc, the hyperlinks all refer to the
original pc. Is there some way, like a Find/Replace
function, that we could edit all the hundreds of
hyperlinks at once to show the new pc's location instead
of the old one?

Thanks!

 

[jeff]

Hi,

YOu might try this macro on a TEST copy of your file
substitutes NewPc for OldPc in all cells in "E1:E10".
You may have to modify a bit to change only those
cells that are hyperlinks.

Private Sub EditHPRLnks()
Dim c As Range
Dim OldPc, NewPc As String
OldPc = "MyOLDPC"
NewPc = "JohnsBkupPC"
For Each c In Range("E1:E10")
x = InStr(1, c, OldPc)
If x Then c.Value = Left(c, x - 1) & NewPc & Right(c, Len
(c) - x - Len(OldPc) + 1)
Next c
End Sub

jeff

 

[Winston]

Thanks for your response. It looked good, but when we
tried it, no hyperlinks were changed. Could you please
review the below and let us know what you think might have
gone wrong?

Private Sub EditHyperlinks()
Dim c As Range
Dim Winston, Jennings As String
OldPc = "Winston"
NewPc = "Jennings"
For Each c In Range("A1:A2000")
x = InStr(1, c, Winston)
If x Then c.Value = Left(c, x - 1) & Jennings & Right(c,
Len c) - x - Len(Winston) + 1)
Next c
End Sub

Regards!
Winston