Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mass Hyperlink Updates (Office 95)

    I need to update the hyperlinks within several worksheets located in a workbook. Does anyone know of a way to update these en masse rather than individually ?

    Many thanks
    Phil

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mass Hyperlink Updates (Office 95)

    Search and replace?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Aug 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mass Hyperlink Updates (Office 95)

    Unfortunately not. This only finds the alternative text that we have associated with the link rather than the text of the link itself.

    Thanks though !

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Mass Hyperlink Updates (Office 95)

    I don't even remember that Excel 95 supported hyperlinks <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    Perhaps a poster with an older version will be able to help.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mass Hyperlink Updates (Office 95)

    You might use this code to find and replace in all hyperlinks of the active worksheet:

    Option Explicit

    Sub ReplaceInHyperlinks()
    Dim oLink As Hyperlink
    Dim sFind As String
    Dim sReplace As String
    sFind = InputBox("Please enter the text to search for", "Find and Replace in Hyperlinks")
    If sFind = "" Then Exit Sub
    sReplace = InputBox("Please enter the text to replace with", "Find and Replace in Hyperlinks")
    If sReplace = "" Then
    If MsgBox("No replace text was entered, continue replacing with nothing?" _
    , vbYesNo, "Find and Replace in Hyperlinks") = vbNo Then Exit Sub
    End If
    For Each oLink In ActiveSheet.Hyperlinks
    If InStr(oLink.Address, sFind) > 0 Then
    oLink.Address = Application.WorksheetFunction.Substitute(oLink.Add ress, sFind, sReplace)
    End If
    Next
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Mass Hyperlink Updates (Office 95)

    Jan,

    I tried your code and was most impressed following my own efforts on this today. I found that the replace worked fine in those hyperlinks that Excel generates itself - such as when you enter a URL and it converts it to a hyperlink.

    However it does not replace entries created with the Hyperlink worksheet function, such as:

    =HYPERLINK("www.wopr.com","Woody's Office Site")

    Thanks for your contributions.

    Peter Moran

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mass Hyperlink Updates (Office 95)

    <hr>However it does not replace entries created with the Hyperlink worksheet function, such as:

    =HYPERLINK("www.wopr.com","Woody's Office Site")
    <hr>

    No, those can be changed using the find and replace function:
    - start Find and select Formulas
    - choose replace and type the find and replace text.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    New Lounger
    Join Date
    Aug 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mass Hyperlink Updates (Office 95)

    The code worked well. Thank you all very much for your suggestions.

    Kind regards

    Phil

Posting Permissions

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