Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 11th 09, 07:05 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 12
Default How Can I Globally Change Hyperlinks?


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

  #2   Report Post  
Old August 11th 09, 07:23 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 12
Default How Can I Globally Change Hyperlinks?


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

Cheers--
Terry--WB4FXD
Edenton, NC
  #3   Report Post  
Old August 11th 09, 08:08 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default How Can I Globally Change Hyperlinks?

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
  #4   Report Post  
Old August 12th 09, 06:35 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 12
Default How Can I Globally Change Hyperlinks?

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
  #6   Report Post  
Old August 13th 09, 06:00 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default How Can I Globally Change Hyperlinks?

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
  #7   Report Post  
Old August 13th 09, 10:15 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 12
Default How Can I Globally Change Hyperlinks?

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
  #8   Report Post  
Old August 13th 09, 10:41 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default How Can I Globally Change Hyperlinks?

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!

Terry wrote:

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


--

Dave Peterson
  #9   Report Post  
Old August 14th 09, 04:27 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 12
Default How Can I Globally Change Hyperlinks?

On Thu, 13 Aug 2009 16:41:57 -0500, Dave Peterson
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, _
Compa=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
  #10   Report Post  
Old August 19th 09, 12:11 AM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default How Can I Globally Change Hyperlinks?

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.

Terry wrote:

On Thu, 13 Aug 2009 16:41:57 -0500, Dave Peterson
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, _
Compa=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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I change the font globally for all comments? karen Setting up and Configuration of Excel 1 March 16th 06 01:51 AM
Globally change relative-addressed hyperlinks to absolute-addr Herman H Excel Worksheet Functions 1 November 20th 05 01:45 AM
change values globally MIke Excel Worksheet Functions 4 November 16th 05 10:15 PM
How can I globally change the formatting of all my comments in Exc Debbe Excel Discussion (Misc queries) 1 December 13th 04 09:49 PM
Edit Hyperlinks Globally [email protected] Excel Worksheet Functions 0 November 3rd 04 03:37 PM


All times are GMT +1. The time now is 04:40 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017