How Can I Globally Change Hyperlinks?

[Terry]

I seem to know just enough about Excel to be dangerous, and therein
lies my question.

I have a spreadsheet (1 sheet) that has some 600 (and climbing) rows,
one column of each having a hyperlink to a file on an external drive.
Eventually, all the linked files will be moved to another disk and the
links changed to that new drive's location.

Is there a way to globally change the links to the other disk when the
project is completed. I have stopped adding links until I discover a
way, for I really don't want to do the changes manually one at a time!

Thanks for any advice.

Cheers--Terry
Terry--WB4FXD
Edenton, NC

[Terry]

My bad! It's Excel 2003 I'm using. Sribouthat!

Cheers--
Terry--WB4FXD
Edenton, NC

[Dave Peterson]

I like to use the =hyperlink() worksheet function for this.

I'll put the path in a hidden cell (A1 in my example). (Include the trailing
backslash, too!)

Then put the filenames in A2:Axxx
Then use the =hyperlink() formula in B2:Bxx.

=hyperlink("File:////"&$a$1&a2,"Click me")
and drag down

Then when I need to change the folder, I can change it one location (A1).

I could embed the path directly in the formula:
=hyperlink("File:////c:\my documents\excel\"& a2,"Click me")
and use Edit|Replace, but that seems like more work to me.

========
If you used Insert|Hyperlink, then you've noticed that edit|replace won't touch
those hyperlink addresses.

Saved from a previous post:

If you used Insert|hyperlink (xl2003 menus), you'll have more work to do. But
the good news is David McRitchie has done most of it for you:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm
look for:
Fix Hyperlinks (#FixHyperlinks)

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

--

Dave Peterson

[Terry]

On Tue, 11 Aug 2009 14:08:05 -0500, Dave Peterson
<pete...@verizonXSPAM.net> wrote:

=>I like to use the =hyperlink() worksheet function for this.

Wow! Thanks, Dave, this looks like the mother lode of linking
information. I've printed your post and will start looking at sources.

Company at the house now and I dast not desert them for fear of
incurring the wrath of She Who Must be Obeyed...!!

Many thanks again.

Cheers--
Terry--WB4FXD
Edenton, NC

[Terry]

On Wed, 12 Aug 2009 17:35:47 GMT, katam...@hotmail.com (Terry)
wrote:

=>On Tue, 11 Aug 2009 14:08:05 -0500, Dave Peterson
=><pete...@verizonXSPAM.net> wrote:
=>
=>=>I like to use the =hyperlink() worksheet function for this.
=>
=>Wow! Thanks, Dave, this looks like the mother lode of linking
=>information....

Dave:--

My ignorance of Excel and VB is obvious! I tried everything in your
post and nothing worked. Gloom...

Here's the present situation:

Excel 2003 Sheet with filenames in A6:A619, each referring to file by
that name.

Hyperlink now for a file is:

file:\\\E:\Cards\All Cards-For Linking\filename.jpg

New desired link is:

file:\\\C:\Cards\SpecialBatch\ALL CARDS-FOR LINKING\filename.jpg

If I manually change one for the other, all is well, but there must be
an easier way. Find/replace works OK when the link info is displayed
as text, but not if just the filename is displayed.

If you have the time, could you take pity on me and show me what to
do?

TIA--
Terry--WB4FXD
Edenton, NC

[Dave Peterson]

So you used the insert|hyperlink version of hyperlinks, right?

This is from David McRitchie's site:

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

Try something like:

Option Explicit
Sub Fix192Hyperlinks()
Dim OldStr As String, NewStr As String
OldStr = "E:\Cards\All Cards-For Linking\"
NewStr = "C:\Cards\SpecialBatch\ALL CARDS-FOR LINKING\"
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

I added the keywords in the replace line and the compare parm.

If you're not familiar with VBA, take a look at those sites in the earlier post.

--

Dave Peterson

[Terry]

On Thu, 13 Aug 2009 12:00:09 -0500, Dave Peterson
<pete...@verizonXSPAM.net> wrote:

=>So you used the insert|hyperlink version of hyperlinks, right?

Yes

=>This is from David McRitchie's site:
=>
=>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 this--no joy

=>Try something like:

=>Option Explicit
=>Sub Fix192Hyperlinks()
=> Dim OldStr As String, NewStr As String
=> OldStr = "E:\Cards\All Cards-For Linking\"
=> NewStr = "C:\Cards\SpecialBatch\ALL CARDS-FOR LINKING\"
=> 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

And this, too. Still no joy.

I entered a file name, added a hyperlink through the Edit function,
and made sure the link was correct. I changed the link to the new link
and checked it, too. OK. Then changed the link back to the old link.

Next, I put the code in the VB edit window by right-clicking "Sheet
1", selecting "View Code", and typing in the code.

I returned to the sheet through "File"/"Close...and Return..."

Old link still there.

Back to the code. F5 to run. Some crunching somewhere I think...

Back to the sheet. No joy. Still the old adress.

Was this the way to do it?

Groan again + Help!

Be patient--I'm old!

Cheers--

Terry--WB4FXD
Edenton, NC

[Dave Peterson]

The code should be placed in a General module (not behind the worksheet).

Inside the VBE
Insert|Module

And move the code there.

Then double check to make sure you have the old link path correct in the code
(as well as the new link path).

Then select the worksheet with the links (the code uses the ActiveSheet).

Then hit alt-F8 and run the macro.

ps. Make sure your slashes match what you used in the hyperlinks. Hyperlinks
can be pretty forgiving, but string comparisons aren't!

--

Dave Peterson

[Terry]

On Thu, 13 Aug 2009 16:41:57 -0500, Dave Peterson
<pete...@verizonXSPAM.net> wrote:

=>The code should be placed in a General module (not behind the
worksheet).
=>
=>Inside the VBE
=>Insert|Module
=>
=>And move the code there.

This the code I am using:

Option Explicit
Sub CardHyperlinks()

Dim OldStr As String, NewStr As String

OldStr = "file:///E:\QSL CARDS\ALL CARDS-FOR LINKING\"
NewStr = "file:///C:\ALL CARDS-FOR LINKING\"
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

=>Then double check to make sure you have the old link path correct in

the code (as well as the new link path).

They match the present and future locations.

=>Then select the worksheet with the links (the code uses the
ActiveSheet).

Only one sheet in this project.

=>Then hit alt-F8 and run the macro.

No Joy. : () ( Still the old (present) link.

I stepped through the code with F8 and no unusual action was seen.

=>ps. Make sure your slashes match what you used in the hyperlinks.

Several times, but they are exactly what the link actually is, all
slashes especially!

I must be doing something wrong. But what?

Cheers--

Terry--WB4FXD
Edenton, NC

[Dave Peterson]

How about copy|pasting a few of the old (untouched) links into your next
message.

Or run some tests against a copy of the worksheet.

Oldstr = "ALL"
newstr = "xxx"

just to see if that's working.

--

Dave Peterson