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

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

Cheers--
Terry--WB4FXD
Edenton, NC

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 he
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.)

Terry wrote:

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

--

Dave Peterson

On Tue, 11 Aug 2009 14:08:05 -0500, Dave Peterson
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

On Wed, 12 Aug 2009 17:35:47 GMT, (Terry)
wrote:

=On Tue, 11 Aug 2009 14:08:05 -0500, Dave Peterson
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

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, _
compa=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.



Terry wrote:

On Wed, 12 Aug 2009 17:35:47 GMT, (Terry)
wrote:

=On Tue, 11 Aug 2009 14:08:05 -0500, Dave Peterson
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

On Thu, 13 Aug 2009 12:00:09 -0500, Dave Peterson
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, _
= compa=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