Changing Server name in Hyperlinks?

[A.]

Recently our server was changed. That was a good thing. The bad thing
is that the hyperlinks in my spreadsheets refer to the old server.

All of the hyperlinks are in one column. I thought that I could do a
find and replace to change the server name, but that didn't work.

Is there a way with VBA (or even with Excel), to change the server name?

As always, any help would be much appreciated.

Anita

[Jake Marx]

Hi Anita,

You could use a VBA function like this to do that:

Sub ReplaceTextInAllHyperlinks(rsLookFor As String, _
rsReplaceWith As String)
Dim ws As Worksheet
Dim hyp As Hyperlink

For Each ws In ThisWorkbook.Worksheets
For Each hyp In ws.Hyperlinks
hyp.Address = Replace$(Expression:=hyp.Address, _
Find:=rsLookFor, Replace:=rsReplaceWith, _
Compare:=vbTextCompare)
Next hyp
Next ws
End Sub

Basically, you just pass in the string to look for and what you want it
changed to, and it will replace all occurrances in all hyperlinks in your
workbook. For example, if your server name changed from "MyServer" to
"MyNewServer", you would use:

ReplaceTextInAllHyperlinks "MyServer", "MyNewServer"

This will work in XL 2000 - if you don't have 2000, I don't think you can
use the Replace$ function (I seem to remember it's new in 2000), so you'll
have to use InStr and some combination of the Left$, Mid$, and Right$
functions.

Regards,
Jake Marx

"A." <gray...@uswest.net> wrote in message
news:3ABC12EF...@uswest.net...

[A.]

Jake,

Thanks! I'll try this on Monday.

Anita