edit multiple hyperlinks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

matthew_h - 21 Mar 2008 19:05 GMT

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!

Reply to this Message

Dave Peterson - 21 Mar 2008 20:05 GMT

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.

> I am working with Excel 2003...
>
[quoted text clipped - 14 lines]
>
> Thanks in advance!

Signature

Dave Peterson

Reply to this Message

matthew_h - 21 Mar 2008 20:33 GMT

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.

> You could use the code from David McRitchie's site:
>
[quoted text clipped - 40 lines]
> >
> > Thanks in advance!

Reply to this Message

Dave Peterson - 21 Mar 2008 21:47 GMT

#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)

> Thanks for the link Dave. I have been looking over McRitchie's macro info.
>
[quoted text clipped - 72 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

matthew_h - 24 Mar 2008 16:20 GMT

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.

> #1. Are the names really New_File_Server and Old_File_Server?
>
[quoted text clipped - 87 lines]
> > >
> > > Dave Peterson

Reply to this Message

Dave Peterson - 24 Mar 2008 17:25 GMT

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!
>
[quoted text clipped - 111 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

matthew_h - 24 Mar 2008 20:31 GMT

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!

> I would add that parameter to the replace function call.
>
[quoted text clipped - 119 lines]
> > >
> > > Dave Peterson

Reply to this Message

Dave Peterson - 24 Mar 2008 21:49 GMT

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:
>
[quoted text clipped - 143 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Rate this thread: