Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Replace Multiple Hyperlink Addresses

  1. #1
    Join Date
    12th July 2007
    Posts
    4

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,020

    Re: Changing Multiple Hyperlinks With A Macro

    Why can't you just use Find and Replace?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    12th July 2007
    Posts
    4

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,020

    Re: Changing Multiple Hyperlinks With A Macro

    Can you attach a small sample workbook?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    12th July 2007
    Posts
    4

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Mathias Schnell; July 12th, 2007 at 05:06.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th July 2007
    Posts
    4

    Re: Changing Multiple Hyperlinks With A Macro

    So is there no solution?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    4,026

    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.
    Regards,
    Barry

    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. #8
    Join Date
    9th January 2007
    Location
    Idaho
    Posts
    115

    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

    VB:
    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 at 08:34.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    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


    VB:
    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 
    
    
    Cheers,

    dr

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

    http://www.ExcelVBA.joellerabu.com

  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Changing Multiple Hyperlinks With A Macro

    I think you only need;
    VB:
    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 
    
    

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Change Hyperlink Addresses En Mass
    By Leogabe in forum EXCEL HELP
    Replies: 3
    Last Post: June 14th, 2008, 01:45
  2. Pull Hyperlink Addresses From Hyperlinks
    By thunderstorm654 in forum EXCEL HELP
    Replies: 5
    Last Post: August 6th, 2007, 23:00
  3. Vba For Multiple Addresses Via Range
    By PHRoG in forum Excel and/or Email Help
    Replies: 10
    Last Post: April 25th, 2007, 05:11
  4. Sending Email To Many Addresses From Outlook Via Hyperlink
    By sugizo in forum Excel and/or Email Help
    Replies: 1
    Last Post: March 22nd, 2007, 19:02
  5. Replies: 1
    Last Post: July 12th, 2005, 09:59

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno