edit multiple hyperlinks

[matthew_h]

I am working with Excel 2003...

I have a excel file that is full of hyperlinks to numerous folders "job"
folders within a single shared folder. The shared folder has been moved to a
new file server.

How can I edit multiple hyperlinks rather than opening each hyperlink and
updating the path to the folder.

The only thing that has changed in the path is the \\Server_Name\ portion.

I need to change from \\Old_File_Server\shared folder\job_folders TO
\\New_File_Server|shared folder\job_folders.

I have a feeling this can only be accomplished with a custom script and
unfortunately I am not a script writer.

Thanks in advance!

 

[Dave Peterson]

You could use the code from 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

=======
This is one reason that I like the =hyperlink() worksheet function.

I can put the common path in a cell (say C1) and put the filenames in column A.
Then put the =hyperlink() function in column B:

In C1:
\\Old_File_Server\shared folder\job_folders\
(with the final backslash)

Then the filename in A1:Axx
and in B1
=hyperlink("file:////" & $c$1 & a1, "Click me")
and drag down.

If the folder changes, I just change C1.

 

[matthew_h]

Thanks for the link Dave. I have been looking over McRitchie's macro info.

I am new to macro writing so it's not making much sense yet.

Here's McRitchie's sample:
Sub Fix192Hyperlinks()
Dim OldStr As String, NewStr As String
OldStr = "http://192.168.15.5/"
NewStr = "http://hank.home.on.ca/"
Dim hyp As Hyperlink
For Each hyp In ActiveSheet.Hyperlinks
hyp.Address = Replace(hyp.Address, OldStr, NewStr)
Next hyp
End Sub

I tried with this version and it did nothing:
Sub Fix192Hyperlinks()
Dim OldStr As String, NewStr As String
OldStr = "\\Old_File_server\Shared_folder\*"
NewStr = "\\New_File_Server\Shared_folder\*"
Dim hyp As Hyperlink
For Each hyp In ActiveSheet.Hyperlinks
hyp.Address = Replace(hyp.Address, OldStr, NewStr)
Next hyp
End Sub

Any ideas or tips to make this work? Thanks again for your time.

 

[Dave Peterson]

#1. Are the names really New_File_Server and Old_File_Server?

If no, then change them to what you really see in the hyperlink.

#2. Don't put that trailing asterisk in the strings, either.

#3. Replace was added in xl2k. What version of excel are you using?

#4. To make sure that upper/lower case differences don't matter, I'd use:

hyp.Address = Replace(expression:=hyp.Address, _
Find:=OldStr, _
Replace:=NewStr, _
compare:=vbTextCompare)

 

[matthew_h]

I'm using Excel 2003. Thanks for helping!

Should I append this : compare:=vbTextCompare to the end of the macro?

The sheet has a Column of cells full of Job Numbers. For example, W0001 and
under that W0002, W0003 and so on. We create a hyperlink in the job number
cell to a shared folder that contains various documents for that specific
job. So each hyperlink is technically different but the beginning path is the
same.

I need to change the hyperlinks from \\Helios\Engineering\Job_Number\ to
\\Atlas\Engineering\Job_Number\ . The Engineering directory was moved to a
new file server.

The Find\Replace function in Excel does not drill down to modify the
embedded hyperlinks. Maybe I'm wrong but I can't seem to make it work with
hyperlinks.

Thanks again for your time and assistance. It is very much appreciated.

 

[Dave Peterson]

I would add that parameter to the replace function call.

And Edit|Replace isn't being used. David McRitchie's code is going through each
hyperlink and doing that replace (which isn't the same as Edit|Replace).

I'm not sure what you tried, but you may want to post the macro you used, how
you ran it and what happened then.

I'm using Excel 2003. Thanks for helping!

Should I append this : compare:=vbTextCompare to the end of the macro?

The sheet has a Column of cells full of Job Numbers. For example, W0001 and
under that W0002, W0003 and so on. We create a hyperlink in the job number
cell to a shared folder that contains various documents for that specific
job. So each hyperlink is technically different but the beginning path is the
same.

I need to change the hyperlinks from \\Helios\Engineering\Job_Number\ to
\\Atlas\Engineering\Job_Number\ . The Engineering directory was moved to a
new file server.

The Find\Replace function in Excel does not drill down to modify the
embedded hyperlinks. Maybe I'm wrong but I can't seem to make it work with
hyperlinks.

Thanks again for your time and assistance. It is very much appreciated.

 

[matthew_h]

Here's what I just ran after reading your last post:

Sub FixHyperlinks()
Dim OldStr As String, NewStr As String
OldStr = "\\Helios\"
NewStr = "\\Atlas\"
Dim hyp As Hyperlink
For Each hyp In ActiveSheet.Hyperlinks
hyp.Address = Replace(expression:=hyp.Address, _
Find:=OldStr, _
Replace:=NewStr, _
compare:=vbTextCompare)
Next hyp
End Sub

This worked! Thanks so much Dave! You are awesome! I had to modify the macro
another time to get some of the links that had an extra path to nowhere and
that worked also. I hope this will get me on my way to doing more macro &
scripting activities. Thanks for sharing your knowlege and your good deed!

 

[Dave Peterson]

Glad you got it working.

I'm sure David McRitchie is happy that you got his code going, too.

Here's what I just ran after reading your last post:

Sub FixHyperlinks()
Dim OldStr As String, NewStr As String
OldStr = "\\Helios\"
NewStr = "\\Atlas\"
Dim hyp As Hyperlink
For Each hyp In ActiveSheet.Hyperlinks
hyp.Address = Replace(expression:=hyp.Address, _
Find:=OldStr, _
Replace:=NewStr, _
compare:=vbTextCompare)
Next hyp
End Sub

This worked! Thanks so much Dave! You are awesome! I had to modify the macro
another time to get some of the links that had an extra path to nowhere and
that worked also. I hope this will get me on my way to doing more macro &
scripting activities. Thanks for sharing your knowlege and your good deed!