Replace Multiple Hyperlink Addresses

  • Hey there, I'm just now working on what appears to be a Macbook (never used Macs before) and I'm working on an Excel file for my job that is going to be pretty tedious for me unless I can come up with some form of automation (a macro).


    Here's what I have to do. Every hyperlink in this excel file has the wrong address connected to it (don't ask). The basic problem is that every hyperlink goes "http://localhost/microsoft user/folder/file" when they should all be "http://localhost/mac user/folder/file" or something like that.


    Now the good part is that I don't have to put the full "http://localhost/mac user/folder/file" because since the Macbook is connected to the server that these links are linking to, I just need to say "folder/file" and the rest behind it is added automatically. THe problem is that I have no way to go to each and every hyperlink and just delete the "http://localhost/microsoft user/" part.


    I've tried using the macro recorder, doing this change to a single cell, then running it on other cells, but it doesn't work and I get errors. I have some programming knowledge, so I tried to go into the VB Script editor to edit the macro myself to my liking, but I don't know VB very well and it'd take me a while to learn it, on top of learning Excel's libraries.


    So could anyone write up a quick macro for me that will go through every cell with a hyperlink and delete that 'http://localhost/microsoft user/" part of the address or teach me how do it?


    Thank you.

  • Re: Changing Multiple Hyperlinks With A Macro


    Because the hyperlinks are not put simply as 'http://...'


    They're dates, names, times, etc. that act as hyperlinks. The link to the file that they represent is a property of the cell or of the text (not sure which). It's not just that the cells have http://... written in them.

  • Re: Changing Multiple Hyperlinks With A Macro


    I probably could, but it's work related documents that I was told to try and keep confidential. I could make up a quick example in excel though and try to show you what I mean.


    Now for this sample, see how the 'Result' column is full of words that are hyperlinks and imagine that I need each hyperlink to have the "C:\\Documents_And_Settings\" taken out of them, leaving the rest of the address. Also imagine that these hyperlinks are not just restricted to one column, but are spread across the entire spreadsheet for hundreds of rows and columns. I think you get the idea.


    Also I made this on my main workstation, which has MS Office 2002 for MS XP, but I'm almost positive that this and the Mac 2003/2004 version I'm using is very similar and utilizes VB Script.

  • Re: Changing Multiple Hyperlinks With A Macro


    Have you tried to record the actions that you need to take to get the hyperlinks to work??


    If so can you post the code... I'm sure that just handing out the file names won't be an issue.

  • Re: Changing Multiple Hyperlinks With A Macro


    Try something like this, as long as the username is the same for all the strings it should work


  • Re: Changing Multiple Hyperlinks With A Macro


    This will search every cell in the used area and replace hyperlink addresses.


    Change only the string you want deleted (keep the quotes). Note I left the final "/" off the replacement string so the new address will start with it!


    As always, try this on a _copy_ of your workbook



  • Re: Changing Multiple Hyperlinks With A Macro


    I think you only need;

    Code
    Sub ReplacePartHyperlinkAddress()
     Dim hLink As Hyperlink
     Dim wSheet As Worksheet
     
        For Each wSheet In Worksheets
         For Each hLink In wSheet.Hyperlinks
            hLink.Address = Replace(hLink.Address, "http://localhost/microsoft user", "")
         Next hLink
        Next wSheet
    End Sub

Participate now!

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