Excel - Find and Replace Hyperlink paths

Hi,

I've honestly searched for a few hours on the internet within different forums and I could not find a resolution for this that worked for me.

Within Excel I have cell's that have hyperlinks to another location on the network and those locations hardcoded in the hyperlink are changing. So instead of manually changing 800 hyperlinks. Anyone have a way to find the beginning portion of a path in the hyperlink and adjust it?


Example:

A cell in excel has a name of "Bookkeeping" which is a hyperlink to \\servername1\scans\username\Bookkeeping.xls

That path \\servername1\scans\username... is no longer valid. The new path is \\servername1\department\accounting\bookkeeping.xls

How can i swap
\\servername1\scans\username\ for
\\servername1\department\accounting\

also when i hover over the cell it shows the path. Will this display be corrected once the paths are swapped?


Thanks for your help and time in advance.
-Mark
LDCServerAdminsAsked:
Who is Participating?
 
Elton PascuaConnect With a Mentor Commented:
Try this (on a backup file).

Sub ReplaceHyperlinks()
    
    Dim hlink As Hyperlink
    Dim ws As Worksheet
    Dim oldLink As String
    Dim newlink As String
    
    Set ws = ActiveSheet
    
    oldLink = "\\servername1\scans\username\"
    newlink = "\\servername1\department\accounting\"

    For Each hlink In ws.Hyperlinks
        hlink.Address = Replace(hlink.Address, oldLink, newlink)
    Next hlink

End Sub

Open in new window

0
 
SteveCommented:
the attached code (with a little tweak) should do what you require...

Sub ReplaceHyperlink()
Dim myR As Range
Dim tempH As String
Set myR = Selection
For Each c In myR
    tempH = c.Hyperlinks(1).Address
    tempH = Replace(tempH, "D:\", "C:\") ' change D:\ to C:\
    c.Hyperlinks(1).Address = tempH
Next
End Sub

Open in new window

0
 
FlysterCommented:
Here's a simple find and replace macro:

Sub ChangePath()

    Cells.Replace What:="\\servername1\scans\username\", Replacement:= _
        "\\servername1\department\accounting\", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Open in new window


Flyster
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
LDCServerAdminsAuthor Commented:
Thanks to you all for posting information. I tried all 3 and none work for me. It might be the way I am doing it.

Here are the steps I'm taking.
Test Excel doc called TEST1 with cell called "TEST2" hyperlinked to TEST2 in current directory.

\\server1\backups\TEST1
\\server1\backups\TEST2
\\server1\backups\zTEST\TEST2

I open TEST1 , macros , name macro + create, enter your code , adjust file paths
oldpath: \\server1\backups\
newpath: \\server1\backups\zTEST
save, then execute the macro. Nothing happens when it's run. try the hyperlink in TEST1 to TEST2 and it still pulls the doc from the old location: \\server1\backups\TEST2


sorry for changing the filename/paths in the examples. At first was just trying to describe it.
0
 
Elton PascuaCommented:
I've tested with a workbook and it seems to be working. Can you test with this workbook?
replace-UNC-hyperlinks.xlsm
0
 
Elton PascuaCommented:
You accepted the answer but I was wondering if you figured out what went wrong? I'm interested to know. :)
0
 
LDCServerAdminsAuthor Commented:
I thought I posted the comment before I awarded the points, but I guess it's that kind of day.

I had said your workbook worked for me. When I put in my values it worked like it should have. I had created the doc as a .xlsm too, but mine for whatever reason mine did not work.

After comparing the macro code from the workbook you sent that worked and the one I had I saw that the "Option Explicit" above Sub ReplaceHyperlinks () was not there. I thought that was the issue and added that. But My doc still can not be editted now. So I definetly closed this to fast. I thought I was set.
This doc has 6 sheets within it instead of the test one we were using having just the one. I'm about to test that now.

Not sure what else could be causing this not to work.
Attached is the code I have listed in the macro...


I'm using Excel 2010 if that matters.
Capture.jpg
0
 
Elton PascuaCommented:
Not sure if there had been changes to the object model with Excel 2010, I can duplicate later on another machine. I coded this 2007.

By the way, use this updated code so it loops through all the sheets.

Option Explicit

Sub ReplaceHyperlinks()
    
    Dim wb As Workbook
    Dim hlink As Hyperlink
    Dim ws As Worksheet
    Dim oldLink As String
    Dim newlink As String
    
    Set wb = ThisWorkbook
    
    oldLink = "\\servername1\scans\username\"
    newlink = "\\servername1\department\accounting\"
    
    For Each ws In wb.Worksheets
        For Each hlink In ws.Hyperlinks
            hlink.Address = Replace(hlink.Address, oldLink, newlink)
        Next hlink
    Next ws
End Sub

Open in new window

0
 
LDCServerAdminsAuthor Commented:
I thought I posted this too, maybe my window times out and doesn't post my comments.

I can get the macro code to work when I create a brand new excel doc and save it as a .xlsm.
However I cannot get the code to work in an existing .xls or .xlsx that is saved as a .xlsm.

Even tried copying the sheets from one .xls to a new .xlsm file, but that didn't work either...
0
 
Elton PascuaCommented:
Can you try this. Just record a macro and then save in the personal workbook. Afterwhich, override the recorded macro with the code I posted above. This should make it available for all workbooks.

http://office.microsoft.com/en-us/excel-help/copy-your-macros-to-a-personal-macro-workbook-HA102174076.aspx

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.