Replace Multiple Hyperlink Addresses

[Mathias Schnell]

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.

[StephenR]

Why can't you just use Find and Replace?

[Mathias Schnell]

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.

[StephenR]

Can you attach a small sample workbook?

[Mathias Schnell]

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.

[Mathias Schnell]

So is there no solution?

[iwrk4dedpr]

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.

[AndrewJ]

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

Sub remove_chars() 
     
    Dim x As Integer 
    Dim y As Integer 
    Dim strAddress As String 
     
    For y = 1 To 10 ' starting and ending columns
         
        For x = 1 To 1000 ' starting and ending rows
             
            strAddress = GetAddress(Cells(x, y)) 
             
            If InStr(strAddress, "C:\Documents_And_Settings\") > 0 Then 
                 
                Cells(x, y).Hyperlinks(1).Address = Right(strAddress, Len(strAddress) - InStr(strAddress, "C:\Documents_And_Settings\") - 25) ' change 25 to the number of chars in the string to remove minus 1
                 
            End If 
             
        Next x 
         
    Next y 
     
End Sub 
 
Function GetAddress(HyperlinkCell As Range) 
    If HyperlinkCell.Hyperlinks.Count > 0 Then 
        GetAddress = HyperlinkCell.Hyperlinks(1).Address 
    End If 
End Function 

[rbrhodes]

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

Option Explicit 
Sub CandCHyperlinx() 
     
    Dim cel As Range 
    Dim rng As Range 
    Dim adr As String 
    Dim delstring As String 
     
     'string to delete: CHANGE ME!  (KEEP quotes!)
    delstring = "http://localhost/microsoft user" 
     
     'get all cells as range
    Set rng = ActiveSheet.UsedRange 
     
     'ignore non hyperlinked cells
    On Error Resume Next 
     
     'check every cell
    For Each cel In rng 
         'skip blank cells
        If cel <> "" Then 
             'attempt to get hyperlink address
            adr = cel.Hyperlinks(1).Address 
             'not blank? then correct it, is blank get next
            If adr <> "" Then 
                 'delete string from address
                adr = Application.WorksheetFunction.Substitute(adr, delstring, "") 
                 'put new address
                cel.Hyperlinks(1).Address = adr 
                 'reset for next pass
                adr = "" 
            End If 
        End If 
    Next cel 
     
End Sub 

[Dave Hawley]

I think you only need;

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