Is there anyway of changing the server name of the UNC path on a hyperlink,
within an excel sheet, after migrating a file server a few excel workbooks
which have multiple hyperlinks in each sheet have not automatically updated
to reflect the new server name.

Can this be changed without having to manually update each link which will
take forever?

You should be able to change them with code which shouldn't take forever.

Do it manually one time to a single hyperlink with the macro recorder turned
on to get the basic code.

--
Regards,
Tom Ogilvy


"Pieman" wrote:

Is there anyway of changing the server name of the UNC path on a hyperlink,
within an excel sheet, after migrating a file server a few excel workbooks
which have multiple hyperlinks in each sheet have not automatically updated
to reflect the new server name.

Can this be changed without having to manually update each link which will
take forever?

Take a look at David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
look for:
Fix Hyperlinks (#FixHyperlinks)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Pieman wrote:

Is there anyway of changing the server name of the UNC path on a hyperlink,
within an excel sheet, after migrating a file server a few excel workbooks
which have multiple hyperlinks in each sheet have not automatically updated
to reflect the new server name.

Can this be changed without having to manually update each link which will
take forever?

--

Dave Peterson

Hi Tom,

Thanks for the quick reply, recording the macro gave me the code of what
happens when 1 cell is changed, for which I can easily change the range for;
however the full hyperlink address is then placed within the marco, which I
do not want. Each link may point to different share's on this new server.

Is it possible to only change the server name that the UNC is relative to
and not the full path?

"Tom Ogilvy" wrote:

You should be able to change them with code which shouldn't take forever.

Do it manually one time to a single hyperlink with the macro recorder turned
on to get the basic code.

--
Regards,
Tom Ogilvy


"Pieman" wrote:

Is there anyway of changing the server name of the UNC path on a hyperlink,
within an excel sheet, after migrating a file server a few excel workbooks
which have multiple hyperlinks in each sheet have not automatically updated
to reflect the new server name.

Can this be changed without having to manually update each link which will
take forever?

I would try something like this:

Sub ChangeLink()
Dim hlink As Hyperlink
Dim s As String
For Each hlink In ActiveSheet.Hyperlinks
s = hlink.Address
If InStr(1, s, "//Server1", vbTextCompare) Then
s = Replace(s, "//Server1", "//Server2")
hlink.Address = s
End If
Next
End Sub


Test his on a copy of your file.

--
Regards,
Tom Ogilvy



"Pieman" wrote:

Hi Tom,

Thanks for the quick reply, recording the macro gave me the code of what
happens when 1 cell is changed, for which I can easily change the range for;
however the full hyperlink address is then placed within the marco, which I
do not want. Each link may point to different share's on this new server.

Is it possible to only change the server name that the UNC is relative to
and not the full path?

"Tom Ogilvy" wrote:

You should be able to change them with code which shouldn't take forever.

Do it manually one time to a single hyperlink with the macro recorder turned
on to get the basic code.

--
Regards,
Tom Ogilvy


"Pieman" wrote:

Is there anyway of changing the server name of the UNC path on a hyperlink,
within an excel sheet, after migrating a file server a few excel workbooks
which have multiple hyperlinks in each sheet have not automatically updated
to reflect the new server name.

Can this be changed without having to manually update each link which will
take forever?

Tom,

Thanks very much for that the code worked (with a slight mod); specifing the
server name with forward slash's ( / ) failed to update the links but simply
changing these to back slash's ( \ ) as per below works at treat...

Sub ChangeLink()
Dim hlink As Hyperlink
Dim s As String
For Each hlink In ActiveSheet.Hyperlinks
s = hlink.Address
If InStr(1, s, "\\Server1", vbTextCompare) Then
s = Replace(s, "\\Server1", "\\Server2")
hlink.Address = s
End If
Next
End Sub

Thaks very much for the quick response, you have saved me a whole load of
hassle.

I can never remember which way they lean <g

--
Regards,
Tom Ogilvy


"Pieman" wrote:

Tom,

Thanks very much for that the code worked (with a slight mod); specifing the
server name with forward slash's ( / ) failed to update the links but simply
changing these to back slash's ( \ ) as per below works at treat...

Sub ChangeLink()
Dim hlink As Hyperlink
Dim s As String
For Each hlink In ActiveSheet.Hyperlinks
s = hlink.Address
If InStr(1, s, "\\Server1", vbTextCompare) Then
s = Replace(s, "\\Server1", "\\Server2")
hlink.Address = s
End If
Next
End Sub

Thaks very much for the quick response, you have saved me a whole load of
hassle.