Replacing hyperlinks

[furious5]

Can anyone help me out with a little rountine.

I have an excel spreadsheet with hard coded links e.g \\server1\drawing\1.pdf

I need to replace all instances of server1, with server2. Any ideas how to do this. I have tried using the find and replace but it doesn't seem to work with hyperlinks.

 

[SkipVought]

furious,

Edit/Replace works for me in a hyperlink. Try Selecting the range you want acted on.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[furious5]

It doesn't for me, I have selected the full range and selected to replace SERVER1 with SERVER2 without any success.

What am I doing wrong then?

 

[furious5]

This seems ok, if you have one or two instances to edit. But I have over 1000 instances in the excel workbook which I need to edit.

Anyone any ideas?

 

[krinid]

You mean it doesn't work properly with the Replace All option? What happens?

 

[furious5]

Thats correct.

If I try to do a find and replace, then I get an error back saying:

Microsoft Excel cannot find the data you're searching for. Check your search options, location and formatting.

I am searching with the following:

Find What: Server1
Replace: Server2
Within: Sheet
Search: By Rows
Look in: Formulas

 

[TonyJollans]

Hi Furious,

You don't by chance have Find Entire Cells Only (or Match Case) checked, do you?

Enjoy,
Tony

 

[furious5]

No, none of these are checked.

 

[TonyJollans]

Hi furious,

Struggling to come up with something here .. Is your display text the same as your hyperlink address? I think Find searches the display text.

Enjoy,
Tony

 

[furious5]

The find function seems to work fine, it I am wanting to find text in the actual cells.

The cell value and the hyperlink are two totally different objects and hence this is why I think that the find function cannot find the string I am wishing to replace.

Everything is spelt correctly, and matches the exact entries.

I guess I need a little VBA, but don't know how to approach this.

 

[TonyJollans]

Hi furious,

If I understand you correctly, the hyperlink address is not displayed in the cell, but that is what you want to make changes to. If so, you will need some VBA to run through the hyperlinks. This should work with 2000, not sure off the top of my head about 97 ..

Dim H as Hyperlink
For Each H in Activesheet.hyperlinks
  H.Address = Replace(H.Address, "Server1", "Server2")
Next

Sorry if I've misunderstood.
Enjoy,
Tony