There might be some chance that local address will be converted as below as
relative path:

.../../../../Matt/Documents and settings/example_file.xls

so, i suggest you try "/bob/"

hope this work.





"fordrules01" wrote:

Yes I have noted this but its still does not seem to change anything.

What i am trying to achieve is to replace a hyperlink that says:
c:\Matt\Documents and settings\example_file.xls
to
C:\Bob\Documents and settings\example_file.xls

Is what i had in the code suitable for this?



"Leung" wrote:

Hi

Please beware that the

h.TextToDisplay might be different from h.Address

The h.Address always come with the "/" as ended, e.g. http://www.msn.com/
However the Text to display might doesn't come with the "/" e.g.
http://www.msn.com

so the display result won't change.

hope this help.

Leung
HK



"fordrules01" wrote:

Hi,

Completely new to VBA in Excel. I've got a large number of excel files
(approx 600) all which contain up to 20 hyperlinks to drawings and other
files contained on a workgrouped computer. Due to the computer crashing we
have had to move all these drawings to another computer and i need to find a
way to update what is potentially 12,000 hyperlinks. (i'm aware that the
setup of these computers is by no means ideal)

Anyway i've tried to copy some vba off the microsoft site with no luck as
yet. If anyone can find the error or has a better solution please let me know.

Code below: (http://support.microsoft.com/default...b;en-us;247507)

Sub HyperLinkChange()
Dim oldtext As String
Dim newtext As String
Dim h As Hyperlink

' These can be any text portion of a hyperlink, such as ".com" or ".org".
oldtext = "/Matt"
newtext = "/Bob"

' Check all hyperlinks on active sheet.
For Each h In ActiveSheet.Hyperlinks
x = InStr(1, h.Address, oldtext)
If x 0 Then
If h.TextToDisplay = h.Address Then
h.TextToDisplay = newtext
End If
h.Address = Application.WorksheetFunction. _
Substitute(h.Address, oldtext, newtext)
End If
Next
End Sub

Cheers