Excel novice needs help with a macro to change hyperlink paths in workbook

The title pretty much sums it up I guess.  I need some step by step help please on how to create a macro that scans my entire worksheet and changes the hyperlink paths to point somewhere else and then how to run the macro (only needs to be done once, a server just moved.)

Something like changing c:\oldpath to c:\morecomplicated\newpath.

Thanks!
We are using Excel2010.  I just don't use excel much in terms of macros, etc, so need a little extra help please.
snyperjAsked:
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.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
something like:

Sub Button1_Click()
    Dim oldPath As String
    Dim newPath As String
    Dim ws As Worksheet
    Dim h As Hyperlink
    
    oldPath = "c:\oldpath"
    newPath = "c:\morecomplicated\newpath"
    
    For Each ws In Worksheets
        For Each h In ws.Hyperlinks
            'Debug.Print h.Address
            h.Address = Replace(h.Address, oldPath, newPath, , , vbTextCompare)
            'h.TextToDisplay = Replace(h.TextToDisplay, oldPath, newPath, , , vbTextCompare)
        Next
    Next
End Sub

Open in new window

try customize accordingly.

Steps to record a macro:
http://www.contextures.com/excel-macro-record-test.html
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
dabug80Commented:
You can also just press CTRL + H - to find and replace. Type in the old website address, then the new website address and click 'change all'.
0
snyperjAuthor Commented:
I tried that, but it only changes what is displayed in the cell, not the underlying link address.
0
snyperjAuthor Commented:
To be  more specific (which I should have been in the first place)... I need something that will change part of the path in the existing hyperlink addresses.

So if the existing link was
c:\oldpath\book1.xls
c:\oldpath\subfolder1\book2.xls

I need something to crawl through and update just the 'oldpath' part of the existing links to
the 'morecomplicated\newpath' value:

c:\morecomplicated\newpath\book1.xls
c:\morecomplicated\newpath\subfolder1\book2.xls
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
u can right click the edit hyperlink and see what's the address displayed there and change accodingly.

the address may not display a full path for u there.
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.