group change of file location in hyperlink 2

[waseem19]

i have thousands of hyperlinks in excel that is linked to files on a network , my problem started when the network name changed , so the address in the hyper link must be changed as well ,, i'm manually changing the address for each file i want to open ,, is there a way to change the address for all the files ,,
the change in all of them is the same, so i thought there must be an automatic way of doing it ,, plz help

 

[FrenchCAD]

hit Ctrl+F and go to the Replace option. Type the original text to replace and then enter replacement text and hit "replace all" button

FrenchCAD
Goodrich Actuation Systems France
Airbus A380 group
cyril.guichard@wanadoo.fr

 

[joerd]

FrenchCad's method works if the hyperlinks are made up by using the =HYPERLINK function. If you have used Insert|Hyperlink (Ctrl-K) from the menu, then you need code like this:

Sub ReplaceHyperlinkLocation()
Dim R As Range, H As Hyperlink, OldURL As String, NewURL As String, A As Variant
Const OldLoc As String = "\temp"
Const NewLoc As String = "\newloc"

For Each R In ActiveSheet.UsedRange
    If R.Hyperlinks.Count > 0 Then
        For Each H In R.Hyperlinks
            OldURL = H.Address
            A = InStr(1, OldURL, OldLoc, vbTextCompare)
            NewURL = Left(OldURL, A - 1) & "\newloc" & Mid(OldURL, A + Len(OldLoc))
            H.Address = NewURL
        Next H
    End If
Next R
End Sub

It goes through the active worksheet, looks for hyperlinks, and manipulates the Address string to replace OldLoc with NewLoc. You should define these constants as suited for your case, OldLoc being the old network path, and NewLoc being the new name.


Regards,

Joerd

 

[EGT01]

While you are changing hyperlinks, you may want to consider using Excel's Hyperlink Base feature. This would work if all of your hyperlinked files reside in the same location. Then, if it every changes again, you only have one change to make.

Go to File/Properties and look on the Summary tab. Here you can enter the directory address where your hyperlinked files reside.

I've used this when I have created an Excel file which contains a summary listing of equipment. Each piece of equipment in my summary file is hyperlinked to that piece of equipment's data file. All those other files reside in the same location. Thus the only difference is their "file name". Example:

c:\directory1\directory2\file1.xls
c:\directory1\directory2\file2.xls
c:\directory1\directory2\file3.xls

You can set your hyperlink base = c:\directory1\directory2
then each individual hyperlink is just the file name
file1.xls, file2.xls, file3.xls, etc

 

[waseem19]

i made the hyperlinks through insert, so i can't do a search and replace ,, and i only want to change part of the address not all of it. so my old files address looks somthing like that

Q:\NNTR1\MK\1.pdf
Q:\NNTR1\MK\2.pdf
Q:\NNTR1\MK\3.pdf

and i want to change them to

Q:\SSTRrtr1\MK\1.pdf
Q:\SSTRrtr1\MK\2.pdf
Q:\SSTRrtr1\MK\3.pdf

if i used the code i will change all the files address into the same new one.

 

[joerd]

Try this:

Sub ReplaceHyperlinkLocation()
Dim R As Range, H As Hyperlink, OldURL As String, NewURL As String, A As Variant
Const OldLoc As String = "\NNTR1"
Const NewLoc As String = "\SSTRrtr1"

For Each R In ActiveSheet.UsedRange
    If R.Hyperlinks.Count > 0 Then
        For Each H In R.Hyperlinks
            OldURL = H.Address
            A = InStr(1, OldURL, OldLoc, vbTextCompare)
            NewURL = Left(OldURL, A - 1) & NewLoc & Mid(OldURL, A + Len(OldLoc))
            H.Address = NewURL
        Next H
    End If
Next R
End Sub

Cheers,

Joerd

 

[waseem19]

thanks joerd very much for your help ,, what was really beneficial that the code you gave me needed an if statement to skip the links that has been already changed manually so i had to start learning VBA for excel to be able to understand your code ,, if you gave me a code that would work I wouldn’t had learned a thing

thanks alot ,, here is the code after i modified it . it ran perfectly.

Sub regchange()
Dim R As Range, H As Hyperlink, OldURL As String, NewURL As String, A As Variant
Const OldLoc As String = "gen1"
Const NewLoc As String = "s01"

For Each R In ActiveSheet.UsedRange
If R.Hyperlinks.Count > 0 Then
For Each H In R.Hyperlinks
OldURL = H.Address
A = InStr(1, OldURL, OldLoc, vbTextCompare)
If A = 0 Then GoTo Line3
NewURL = Left(OldURL, A - 1) & NewLoc & Mid(OldURL, A + Len(OldLoc))
H.Address = NewURL
Line3: Next H
End If
Next R
End Sub

there is however one thing i still don't understand, each time the code executes it reads first lots of cells without hyperlinks in them, off course it only takes few seconds to run, but the problem is when i want to debug ,, how do i make it go directly to the cells with hyperlinks and only to those?

 

[FrenchCAD]

Guess VBA does need to read all cells to know which ones have an hyperlink ;-)

FrenchCAD
Goodrich Actuation Systems France
Airbus A380 group
cyril.guichard@goodrich.com

 

[joerd]

You guys got me thinking a little more: hyperlinks can also be related to the worksheet. So the following could be running faster (haven't checked it), where the loop through all the cells has been eliminated:

Sub regchange()
Dim R As Range, H As Hyperlink, OldURL As String, NewURL As String, A As Variant
Const OldLoc As String = "gen1"
Const NewLoc As String = "s01"

Set R = ActiveSheet.UsedRange 'removed for each R loop
If R.Hyperlinks.Count > 0 Then
For Each H In R.Hyperlinks
OldURL = H.Address
A = InStr(1, OldURL, OldLoc, vbTextCompare)
If A = 0 Then GoTo Line3
NewURL = Left(OldURL, A - 1) & NewLoc & Mid(OldURL, A + Len(OldLoc))
H.Address = NewURL
Line3:
Next H
End If
'Next R removed
End Sub


Cheers,

Joerd