Find and Replace Hyperlink Contents

[Dede]

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]

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

 

[Guest]

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]

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

 

[David McRitchie]

Hi Dede,

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

 

[Guest]

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.

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

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.

 

[David McRitchie]

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.

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.

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

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.

 

[Guest]

Thanks David,

I changed the code again and added the slashes but the hyperlink still does
not change when I run the macro.

I don't want the text value of the cell to change, just the hyperlink. Any
idea what I am doing wrong?

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.

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.

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

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]

You may want to post your code.

Thanks David,

I changed the code again and added the slashes but the hyperlink still does
not change when I run the macro.

I don't want the text value of the cell to change, just the hyperlink. Any
idea what I am doing wrong?

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.

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.

:

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

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.

 

[David McRitchie]

Hi Dede,
and what you see with Ctrl+K (insert hyperlink) for the
Type the file or Web page name:
Text to display:
ScreenTip: (if not empty)
also what you see on the formula bar if not the same as Text to display

The slashes or periods added would not make it work if it is not working
-- that suggestion was just to prevent you from making inadvertent changes.

To make sure you installed in correct place include this line of code
Msgbox "installed correctly, now what?"
You should be running as standalone from Alt+F8 to invoke macro
and there should only be one macro with that name shown.

Can't imagine that you would have anything in the following, unless you
inherited the workbook, but check anyway.
file, properties, summary, hyperlink base:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm#FixHyperlinks

 

[Guest]

I am starting to think maybe I have my code in the wrong place. When I open
the sheet and do Alt+F11. I double-click on "This Workbook" on the left hand
side. This opened a window that has a drop-down for "General" and "Workbook"
and I tried running the code in both places.

Am I putting the code in the wrong place?

 

[Dave Peterson]

Actually, the code belongs in a General module.

Select your project in the VBE's project explorer.
Then Insert|module.
Then paste there.

But if you actually ran that code (F5 or Run|Run Sub from within the VBE), it
should have worked ok.

How did you run the code (well, if you did run the code)?

And if it still doesn't work, you still may want to paste your code. (Remember
that you have to match case or code around it.)