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.

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

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

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.

=>On Tue, 11 Aug 2009 14:08:05 -0500, Dave Peterson

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?

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.

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

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!

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

Or run some tests against a copy of the worksheet.

Oldstr = "ALL"
newstr = "xxx"

just to see if that's working.