Edit Multiple Hyperlinks in an Excel 2007 document

I have about 300 rows of data in an excel 2007 document that contain a hyperlink to another location.  The location (server) changed names and is somewhat a more complex name.

Is there a quick solution for changing the location without editing each of the 300 rows?  The foldername is not changing.

For example:
Prior to the change - the Hyperlink showed:
file:\\\//servername01\foldername

The location has changed to:
file:\\\//domain.nnn\groupname01\servername01\groupname\foldername

Any help provided would be appreciated.
Thanks
mmj1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AdrienneSperberTech Support CoordinatorCommented:
Found this VB script that seemed to work for others..

Sub ReplaceHyperlinkAdresses() 
    Dim hypLink As Hyperlink 
    Dim ws As Worksheet 
     
    For Each ws In Worksheets 
        For Each hypLink In ws.Hyperlinks 
            If hypLink.Address Like "file:\\\//servername01\foldername*" Then 
                hypLink.Address = _ 
                Replace(hypLink.Address, "file:\\\//servername01\foldername", "file:\\\//domain.nnn\groupname01\servername01\groupname\foldername") 
            End If 
        Next hypLink 
    Next ws 
End Sub 

Open in new window

0
mmj1Author Commented:
Can you elaborate on how to setup the script - is it similar to setting up a macro?
0
KwoofCommented:
in excel 2007...if developer tab not showing...click the office button, go to excel options, click popular, and check the "Show Developer tab in the Ribbon" and click OK

In the developer tab click "View Code"

Copy and paste the code from AdrienneSperber.

Then in your view tab go to macros and run the code
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

[ fanpages ]IT Services ConsultantCommented:
Hi,

Does the in-built "Search & Replace" [CTRL]+[H] function not allow you to change the hyperlinks?

---
Find what: file:\\\//servername01\foldername
Replace with: file:\\\//domain.nnn\groupname01\servername01\groupname\foldername

Within: Sheet
Search: By Rows
Look in: Formulas
---

Have you already tried this?

BFN,

fp.
0
mmj1Author Commented:
I tested the VB script on a test document (in Excel 2010) with no success - I ran the code and nothing changed (no error messages either).   I also tried the find and replace but that did not work.
0
[ fanpages ]IT Services ConsultantCommented:
Hi again,

Would it be possible to provide a cut-down sample of some of the hyperlinks in a standalone workbook so we can see why the code provided above is not functioning as intended?

(I can also see why the Search & Replace method is failing too).

Thanks.
0
mmj1Author Commented:
Attached is the document that I am testing to see if the script will work.  I would appreciate if you could review the code.  When I run the macro I do not receive any type of error but nothing changes in my hyperlinks.

Quick comment -- I may have said this earlier but just want to make sure I said it correctly - the document I am working with has over 300 different hyperlinks and they do point to different folder names and document names -- but the constant info that I am trying to change is the server name and the group name -- everything after the word "projects" in the hyperlinks do not need to change

Servername01\group\john doe\projects\*
TEST2.xlsm
0
[ fanpages ]IT Services ConsultantCommented:
Hi again,

Sorry for the delay.  I missed the notification of your last comment.

I have looked at your workbook, & changed AdrienneSperber's code accordingly:

Sub ReplaceHyperlinkAdresses()

  Dim hypLink                                           As Hyperlink
  Dim ws                                                As Worksheet
     
  For Each ws In Worksheets
      
      For Each hypLink In ws.Hyperlinks
            
          If hypLink.Address Like "\\Servername01\group\john doe\projects\*" Then
             hypLink.Address = Replace(hypLink.Address, _
                                       "Servername01\group\john doe\projects\", _
                                       "domain.nnn\groupname01\servername01\groupname\foldername\")
          End If
          
      Next hypLink
      
  Next ws
  
End Sub

Open in new window


Please replace the existing routine within your workbook with the one above, & try again.

Thanks.

BFN,

fp.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mmj1Author Commented:
The good news - I changed the code as you indicated above and it WORKED!! THANK YOU!!

The bad news - I have another excel document (same exact scenario) and when I try to run the script - I get an Out of Memory message -- any suggestions on getting this one to work properly?

Thank you.
0
[ fanpages ]IT Services ConsultantCommented:
This could be due to how many hyperlinks are present, the size of the workbook, the amount of Random Access Memory [RAM] (or, combined with MS-Windows swap file space) you have available, what applications are running concurrently, or any number of other reasons; maybe even restarting (re-booting, not just log-off/log-on again) your PC will help.
0
mmj1Author Commented:
I did reboot and try the macor again - same message.  I will try to work on making the workbook smaller.  Thanks for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.