How can I do a Find for all hyperlinks in my spreadsheet (or the entire
workbook) that contain "iis" in they hyperlink and Replace that portion
of they hyperlink with "iis2"?

For hyperlinks that were inserted via Insert|Hyperlink?

If yes, then visit David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
look for:
Fix Hyperlinks (#FixHyperlinks)

Actually, here's David's code:

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

'To make it run against all worksheets, run this macro:

sub DoItAll()
dim Wks as worksheet
for each wks in activeworkbook.worksheets
wks.select
call Fix192Hyperlinks
next wks
end sub

====
This line:
hyp.Address = Replace(hyp.Address, OldStr, NewStr)
uses Replace which was added in xl2k.

If you're using xl97, then change that line to:
hyp.Address = application.substitute(hyp.Address, OldStr, NewStr)

And both Replace and application.substitute are case sensitive.

If you have mixed case, maybe something like:
hyp.Address = Replace(ucase(hyp.Address), ucase(OldStr), NewStr)
(or application.substitute for xl97.)






Dede wrote:

How can I do a Find for all hyperlinks in my spreadsheet (or the entire
workbook) that contain "iis" in they hyperlink and Replace that portion
of they hyperlink with "iis2"?

--

Dave Peterson

How do I go about putting this code in? I was trying to use Find and Replace
from the menu. I am not familiar with inserting code.

"Dave Peterson" wrote:

For hyperlinks that were inserted via Insert|Hyperlink?

If yes, then visit David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
look for:
Fix Hyperlinks (#FixHyperlinks)

Actually, here's David's code:

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

'To make it run against all worksheets, run this macro:

sub DoItAll()
dim Wks as worksheet
for each wks in activeworkbook.worksheets
wks.select
call Fix192Hyperlinks
next wks
end sub

====
This line:
hyp.Address = Replace(hyp.Address, OldStr, NewStr)
uses Replace which was added in xl2k.

If you're using xl97, then change that line to:
hyp.Address = application.substitute(hyp.Address, OldStr, NewStr)

And both Replace and application.substitute are case sensitive.

If you have mixed case, maybe something like:
hyp.Address = Replace(ucase(hyp.Address), ucase(OldStr), NewStr)
(or application.substitute for xl97.)






Dede wrote:

How can I do a Find for all hyperlinks in my spreadsheet (or the entire
workbook) that contain "iis" in they hyperlink and Replace that portion
of they hyperlink with "iis2"?

--

Dave Peterson

Take a look at David's notes:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Dede wrote:

How do I go about putting this code in? I was trying to use Find and Replace
from the menu. I am not familiar with inserting code.

"Dave Peterson" wrote:

For hyperlinks that were inserted via Insert|Hyperlink?

If yes, then visit David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
look for:
Fix Hyperlinks (#FixHyperlinks)

Actually, here's David's code:

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

'To make it run against all worksheets, run this macro:

sub DoItAll()
dim Wks as worksheet
for each wks in activeworkbook.worksheets
wks.select
call Fix192Hyperlinks
next wks
end sub

====
This line:
hyp.Address = Replace(hyp.Address, OldStr, NewStr)
uses Replace which was added in xl2k.

If you're using xl97, then change that line to:
hyp.Address = application.substitute(hyp.Address, OldStr, NewStr)

And both Replace and application.substitute are case sensitive.

If you have mixed case, maybe something like:
hyp.Address = Replace(ucase(hyp.Address), ucase(OldStr), NewStr)
(or application.substitute for xl97.)






Dede wrote:

How can I do a Find for all hyperlinks in my spreadsheet (or the entire
workbook) that contain "iis" in they hyperlink and Replace that portion
of they hyperlink with "iis2"?

--

Dave Peterson


--

Dave Peterson

Hi Dede,

see Getting Started with Macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dede" wrote ...
How do I go about putting this code in? I was trying to use Find and Replace
from the menu. I am not familiar with inserting code.

First of all, thanks for all the help!

I added the code and it ran but nothing changed. I am wondering about the
"old" and "new" path statements. My hyperlink is not an "http" link; mine
links to a file on a network server. My hyperlink starts off with "file:///"
I am only wanting to change the portion of the hyperlink that contains the
word "iis" to "iis2" so in my code I put for the OldStr = "iis" and the
NewStr = "iis2". Is that correct? After I ran the code, the path still
displays "iis" in the hyperlink.

"David McRitchie" wrote:

Hi Dede,

see Getting Started with Macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dede" wrote ...
How do I go about putting this code in? I was trying to use Find and Replace
from the menu. I am not familiar with inserting code.

Hi Dede,
I think your substitutions in the code look correct.

The macro will only change the hyperlink, what you see with
insert hyperlink (ctrl+k), it is not set up to change the cell
value (text value). If you change the hyp.TextToDisplay
you will wipe out formulas, so was not included.

BTW, I would include periods and slashes immediately next
to your old and new strings to reduce possibiliy of collateral damage
to other portions of your links, including inadvertent use in the wrong
workbook.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dede" wrote in message ...
First of all, thanks for all the help!

I added the code and it ran but nothing changed. I am wondering about the
"old" and "new" path statements. My hyperlink is not an "http" link; mine
links to a file on a network server. My hyperlink starts off with "file:///"
I am only wanting to change the portion of the hyperlink that contains the
word "iis" to "iis2" so in my code I put for the OldStr = "iis" and the
NewStr = "iis2". Is that correct? After I ran the code, the path still
displays "iis" in the hyperlink.

"David McRitchie" wrote:

Hi Dede,

see Getting Started with Macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dede" wrote ...
How do I go about putting this code in? I was trying to use Find and Replace
from the menu. I am not familiar with inserting code.