?
Solved

Excel - Find and Replace Hyperlink paths

Posted on 2012-08-21
10
Medium Priority
?
931 Views
Last Modified: 2012-08-22
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
0
Comment
Question by:LDCServerAdmins
10 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 38317693
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
 
LVL 8

Accepted Solution

by:
Elton Pascua earned 2000 total points
ID: 38317695
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
 
LVL 22

Expert Comment

by:Flyster
ID: 38317701
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:LDCServerAdmins
ID: 38317829
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
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38317849
I've tested with a workbook and it seems to be working. Can you test with this workbook?
replace-UNC-hyperlinks.xlsm
0
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38317897
You accepted the answer but I was wondering if you figured out what went wrong? I'm interested to know. :)
0
 

Author Comment

by:LDCServerAdmins
ID: 38318293
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
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38318436
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
 

Author Comment

by:LDCServerAdmins
ID: 38322643
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
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38323414
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question