No announcement yet.

Replace Multiple Hyperlink Addresses

  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    Re: Changing Multiple Hyperlinks With A Macro

    Why can't you just use Find and Replace?


    • #3
      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.


      • #4
        Re: Changing Multiple Hyperlinks With A Macro

        Can you attach a small sample workbook?


        • #5
          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.
          Attached Files
          Last edited by Mathias Schnell; July 12th, 2007, 04:06.


          • #6
            Re: Changing Multiple Hyperlinks With A Macro

            So is there no solution?


            • #7
              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.

              My Favorite New Thing:
              Dynamic Named Ranges

              The alternative for
              "Press Any Key To Continue."

              and we all have one we'd like to use it on

              1. Cross Posting Etiquette
              2. Are You Here To Learn: What Have You Tried?
              3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!


              • #8
                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

                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
                Last edited by AndrewJ; July 12th, 2007, 07:34.


                • #9
                  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

                  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


                  "Questions, help and advice for free, small projects by donation. large projects by quote"



                  • #10
                    Re: Changing Multiple Hyperlinks With A Macro

                    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


                    • #11
                      Re: Replace Multiple Hyperlink Addresses

                      Dangit, of course the experts have to come in and trump me

                      Good stuff tho guys!