Update many excel hyperlinks 1

[mflancour]

I have an excel spreadsheet with hundreds of hyperlinks. They are all wrong and need to be updated. My idea is to write a formula in a new column to update them. The best I can come up with is something like this;

Origional field shows: maskingname
Origional field hyperlink: \\path\filename.txt
New field:
="\\new file path here\"&right($J1,9)

Where "right($J1,9)" is a reference to the file name contained in the existing incorect hyperlink.

The problem is this simply gives me a text field, it has the right path, but is not a hyperlink. Also I would loose the Masking text that was in the origional field. If you don't think there is a way to do this in a formula but know how to do it with vba that would be helpful as well.

Thanks in advance

 

[SkipVought]

Hi,

Try this with the hyperlinks collection...

Sub NewLinks()
    Dim hl As Hyperlink
    For Each hl In ActiveSheet.Hyperlinks
        With hl
            ActiveSheet.Hyperlinks.Add _
                Anchor:=.Range, Address:=Right(.Address, 9), _
                TextToDisplay:=Right(.Address, 9)
        End With
    Next
End Sub

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mflancour]

This code looks like it's exactly what I need. I can't get it to work though. Plus, if I'm understanding the intent, I need to narow the cells searched to a single column. So say the following information is true.

Excel 97
Range of current incorrect links: (F1:F500)
Sheet name: Sheet1
New addresses: "\\sys1\tec\apps\msoffice\template\" & right(.address, 9)

The error I'm receiving with the current code is: "Application-defined or object-defined error". It occures in the "With" portion.

 

[SkipVought]

Did you cut and paste the entire routine? the variable is HL in lower case (short for hyperlink) or any other name you want.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Are thre other hyperlinks on the sheet that do not need to be updated?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mflancour]

First off, thanks for helping so much.

In answere to your questions, yes I pasted the whole thing, and yes I have other links in another column that should not be updated.

I have been playing with the code you gave me some more and when I used the help to lookup Add-Hyperlink it showed the following:

object.Add(Anchor, Address, SubAddress)
object: Required. An expression that returns a Hyperlinks object.
Anchor: Required Object. The anchor for the hyperlink. Can be either a Range or Shape object.
Address: Required String. The address of the hyperlink.
SubAddress Optional Variant. The subaddress of the hyperlink.

I didn't see anything for "TexttoDisplay", so I tried taking that out and I no longer receive the error. Is there another way to do that part, or is there something I need do to get it to work the way it is?

 

[mflancour]

Oh, I ran it without the "TextToDisplay" and it didn't over right what was there, so that part is not a problem. Now I just need to limit the search to a specific column.

 

[SkipVought]

If hl.Range.Column = 6 Then
'replace the link
End If

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[lambuhere1]

Similar to the above requirement,

I have something like this.

All the links in my file have to a particular file

like c:\abc\

I would like to replace it to

c:\n\abc
Can anyone guide me in replacng all the links with the new path.

Thanks

Ram P

 

[xlbo]

Ram - please start a new thread for this - don't "piggyback" other people's threads

The answer (if your links are all formulae) is to use a FIND / REPLACE in formulae

Select the area with the formulae you want to change
CTRL+F
m,ake sure the dropdown box says FORMULAS not values.
enter "C:\abc" inthe find box and
"C:\n\abc" in the replace box
et voila

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde