group change of file location in hyperlink
group change of file location in hyperlink
(OP)
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
the change in all of them is the same, so i thought there must be an automatic way of doing it ,, plz help





RE: group change of file location in hyperlink
FrenchCAD
Goodrich Actuation Systems France
Airbus A380 group
cyril.guichard@wanadoo.fr
RE: group change of file location in hyperlink
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
RE: group change of file location in hyperlink
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
RE: group change of file location in hyperlink
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.
RE: group change of file location in hyperlink
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
RE: group change of file location in hyperlink
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?
RE: group change of file location in hyperlink
FrenchCAD
Goodrich Actuation Systems France
Airbus A380 group
cyril.guichard@goodrich.com
RE: group change of file location in hyperlink
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