Delete part of hyperlink (for all hyperlinks in a worksheet)

    Hi,


    Hoping someone can come to the rescue as I've recently moved a lot of my data from an external drive onto the PC hard drive.


    All links from within my worksheet no longer work :( I've Googled but nothing seems to match what I need so I have been doing it manually (another 600 entries to go!)


    It seems if I remove "..\..\..\AppData\Roaming\Microsoft\Excel\" from the existing hyperlink, then all works fine. But this is now time consuming considering the amount I have left to do.


    Does anyone know a way of removing "..\..\..\AppData\Roaming\Microsoft\Excel\" from all hyperlinks within a worksheet automatically?


    Thanks

    Re: Delete part of hyperlink (for all hyperlinks in a worksheet)


    Something like...
    [vb]
    Sub x()

    Dim h As Hyperlink

    For Each h In ActiveSheet.Hyperlinks
    h.Address = Replace(h.Address, "..\..\..\AppData\Roaming\Microsoft\Excel\" , "")
    '// h.TextToDisplay = "Something Else"
    Next

    End Sub
    [/vb]


    Please TEST this on a copy of your data - the code works, whether that's what you *really* wanted to do is something else altogether!

    Re: Delete part of hyperlink (for all hyperlinks in a worksheet)


    Hi yes it works thanks.


    Apologise for my lack of knowledge, should the For and Next statements step through to the last hyperlink?


    At the moment I have place the code into an active x button and I was expecting one click to do the lot. However I have to keep doing multiple clicks.


    Thanks again.

    Re: Delete part of hyperlink (for all hyperlinks in a worksheet)


    It should work for the ActiveSheet... and process all hyperlinks in that sheet.


    You could put the code in it's own module, rename the procedure to something like
    [vb]
    Public Sub RenameLinks
    [/vb]


    and call RenameLinks from the button Click event


    If you're having issues getting it to process everything, post an anonymous/cut-down copy of your workbook so the code can be seen in context. I won't be able to look at it though, securtity policies here prevent me downloading anything.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login