×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

group change of file location in hyperlink
2

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

RE: group change of file location in hyperlink

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

RE: group change of file location in hyperlink

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

RE: group change of file location in hyperlink

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

RE: group change of file location in hyperlink

(OP)
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.
 

RE: group change of file location in hyperlink

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

RE: group change of file location in hyperlink

(OP)
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?

RE: group change of file location in hyperlink

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

RE: group change of file location in hyperlink

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources