Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel Change All Hyperlinks

Posted on 2011-10-07
9
Medium Priority
?
386 Views
Last Modified: 2012-05-12
I have the following code however it does not change the hyper link

Sub changeHYperlinks()
Dim hyp As Hyperlink
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Sheets
    For Each hyp In sh.Hyperlinks
        hyp.Address = Replace(hyp.Address, "file:///C:\Documents and Settings\hsmith\Application Data\Microsoft\Excel", "file:///\\corp-win\DATA\Legal")
        hyp.TextToDisplay = Replace(hyp.TextToDisplay, "file:///C:\Documents and Settings\hsmith\Application Data\Microsoft\Excel", "file:///\\corp-win\DATA\Legal")
    Next hyp
Next sh
End Sub

I have tried it without the "file:///" but still no luck.  Attached is a copy of my work.  Please try to make it work.

If you hover of the link it states "file:///C:\Documents and Settings\hsmith\Application Data\Microsoft\Excel"

however if you right click it and choose edit link this is what i see and i have tired this path as well.

..\..\hsmith\Application%20Data\Microsoft\Excel\Contract%20Reviews\Chicago\Alps%20Construction%20Master%20Contract.pdf

Contract-Review-Tracker-EE.xlsm
0
Comment
Question by:ansonindustries
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 36933785
Hi, ansonindustries.

Drop all occurrences of "file:///".

Regards,
Brian.
0
 

Author Comment

by:ansonindustries
ID: 36933786
I did that and still no luck.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36933816
ansonindustries,

It worked for me. To be sure, to be sure, Please try this...
Sub changeHYperlinks()
Dim hyp As Hyperlink
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Sheets
    For Each hyp In sh.Hyperlinks
        hyp.Address = Replace(hyp.Address, "C:\Documents and Settings\hsmith\Application Data\Microsoft\Excel", "\\corp-win\DATA\Legal")
        hyp.TextToDisplay = Replace(hyp.TextToDisplay, "C:\Documents and Settings\hsmith\Application Data\Microsoft\Excel", "\\corp-win\DATA\Legal")
    Next hyp
Next sh
End Sub

Open in new window

Thanks,
Brian.  
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:ansonindustries
ID: 36933833
I dont know what to tell you its not working for me.  can you send me your copy?
0
 
LVL 14

Accepted Solution

by:
Tommy Kinard earned 2000 total points
ID: 36933849
I removed the file:/// but I also change the replace function a little please see attached. Contract-Review-Tracker-EE.xlsm
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36933860
use

        hyp.Address = Replace(hyp.Address, "../../hsmith/Application Data/Microsoft/Excel/Contract Reviews/Atlanta/", "file:///\\corp-win\DATA\Legal\")
0
 

Author Closing Comment

by:ansonindustries
ID: 36933867
thanks.  i dont know why it wont work for me.  ill worry about it later.  thanks again though.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36933882
also maybe you need to put the same thing into TextToDisplay

        hyp.Address = Replace(hyp.Address, "../../hsmith/Application Data/Microsoft/Excel/Contract Reviews/Atlanta/", "file:///\\corp-win\DATA\Legal\")
        hyp.TextToDisplay = hyp.Address
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 36933949
Thanks for the points and the grade.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

577 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