Need to Globally chnage hyperlinks in Excel 4

[jryan3]

We have an excel spreadsheet that has many hyperlinks to bookmarks within the same word document. We have replaced the word document by an excel spreadsheet containing the same bookmarks. I am trying to do a global replace of all hyperlinks to point to the new document. When I use the edit replace feature it does not find the hyperlinks.
Is there any way, other than manual, that I can do this global subsitution?

 

[GlennUK]

Hi jryan3,

you say:

We have replaced the word document by an excel spreadsheet containing the same bookmarks.

Can you explain exactly what these Excel bookmarks are?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[PeterMoran]

Hi,

Try the following url on the WOPR Excel board:

http://www.wopr.com/cgi-bin/w3t/sho...ber=211304&page=&view=&sb=&o=&vc=1#Post211304

Good Luck!

Peter Moran

 

[jryan3]

To: PeterMoran
I was unable to get to the 211304 post on the WOPR site.

To: GlennUK
We had two word documents. One of the Word documents had bookmarks in it. The other word document contains hyperlinks to bookmarks in the other word document. We are switching both documents from word to excel. We selected the tables in the word documents and pasted them into an Excel spreadsheet. When we did this the Bookmark came over into the Excel from Word as did the hyperlinks. We now must change all of the links to point to the Excel spreadsheet instead of the word document. Thus our dilemma.
Joe

 

[xlbo]

When we did this the Bookmark came over into the Excel from Word as did the hyperlinks

How did you verify this ?? I was under the impression that there was no actual Bookmark object in excel...

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Yes, Geoff, that's exactly what I was getting at too.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[PeterMoran]

Hi,

Here is the code from Jan Karel Pieterse in the WOPR url I indicated:

You might use this code to find and replace in all hyperlinks of the active worksheet:

Option Explicit

Sub ReplaceInHyperlinks()
Dim oLink As Hyperlink
Dim sFind As String
Dim sReplace As String
sFind = InputBox("Please enter the text to search for", "Find and Replace in Hyperlinks")
If sFind = "" Then Exit Sub
sReplace = InputBox("Please enter the text to replace with", "Find and Replace in Hyperlinks")
If sReplace = "" Then
If MsgBox("No replace text was entered, continue replacing with nothing?" _
, vbYesNo, "Find and Replace in Hyperlinks") = vbNo Then Exit Sub
End If
For Each oLink In ActiveSheet.Hyperlinks
If InStr(oLink.Address, sFind) > 0 Then
oLink.Address = Application.WorksheetFunction.Substitute(oLink.Address, sFind, sReplace)
End If
Next
End Sub


Jan Karel Pieterse
Microsoft Excel MVP, WMVP
The Excel MVP Page | Microsoft MVP Program

Good Luck!

Peter Moran

 

[TonyJollans]

When you copy a range containing bookmarks from Word to Excel, the Word bookmarks get converted to Excel Names.

The posted code looks good. Alternatively, depending on your version, you should be able to do Alt+Shift+F11 and then do a global Find and Replace on the HTML.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[PeterMoran]

Hi,

Some further explanation may assist:

The code supplied above works fine on those hyperlinks that Excel generates itself - such as when you enter a URL and Excel converts it to a hyperlink.

However it does NOT replace entries created with the Hyperlink worksheet function, such as:

=HYPERLINK("

http://www.tek-tips.com","Tek-Tips

Forums")

But these can be found with the normal Find/Replace.

Good Luck!

Peter Moran

 

[jryan3]

PeterMoran & TonyJollans
I appreciate the help! I am getting closer. I am in the process of trying the code, Got interrupted.
I did try the Alt+Shift+F11 and then did a global Find and Replace on the HTML. It looked good. However, When I saved the spreadsheet and then opened it back up and tried the hyperlinks, they didn't work. When I moved my mouse over the hyperlink, the path displayed was to somewhere on my local C: drive temp area and not the network drive where the spreadsheets reside. When I went back in via Alt+Shift+F11, the hyperlinks still looked good. I am now trying to determine what I need to correct to point to the area on the network drive.
Thanks,
joe

 

[jryan3]

PeterMoran, I used the code and it successfully changes the address of the hyperlink. Thank you.
I tried to change the hyperlink name as well by adding the line
oLink.Name = Application.WorksheetFunction.Substitute(oLink.Name, sFind, sReplace)
I get an error telling me " Wrong number of arguments or invalid property assignment". I am not sure, how the hyperlink(1).name field is used. Is it sufficient that I correct the address and subaddress?

Joe

 

[xlbo]

Star to Tony for that info - cheers

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi jryan3,

Just when you think you've got a grip on something, along comes Microsoft to bite you!

Hyperlink bases are an annoyance, to put it mildly. Before doing the Find/Replace set the hyperlink base to something - anything at all - it doesn't need to be valid, just non-blank. Then do the Find/Replace. Then set the hyperlink base back to blank.

As for your other error, the hyperlink Name property is read only - address and sub-address state where to link to and texttoDisplay states the text to display.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[jryan3]

ALL,
It feels so good when you finally stop banging your head against the wall.
Tony, The changing of the Hyperlink base did the trick.
Thanks!

Joe