Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Dec 2003
    Location
    San Francisco, California
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating Broken Hyperlinks (Office XP)

    Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>

    Hi, all. I would appreciate your help with repairing multiple broken hyperlinks in an Excel XP/2002 workbook. I've searched the forum and several of the MVPS sites. I've tried Pieterse's macro for Excel 95 (<post#=211636>post 211636</post#>), but it did not work. I still get the error message "The address of the site is not valid." Search | Replace does not work, either.

    The workbook contains hyperlinks to PDF documents on a network drive (but not part of a document management system). When a user remotely accesses the workbook and saves it (instead of closing it), it breaks all the hyperlinks. The workbook itself is part of a document management system and is accessed by a number of people.

    Is there any way to fix the multiple (several hundred on multiple tabs, at this point) hyperlinks without re-entering each one? The Edit | Links option is grayed out in Excel after our latest system upgrade, so I don't think that's available. Editing the hyperlink shows that it is now pointed to a local folder on the hard drive, instead of to the network folder/drive.

    Any thoughts and suggestions are greatly appreciated. To avoid this problem in the future, I will try locking the specific cells. We cannot make the entire workbook read only, which would really take care of this problem.

    Thank you.

    Brent

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating Broken Hyperlinks (Office XP)

    What happened when you ran Pieterse's macro? Did you get an error message?
    If the macro ran to completion, had the tooltip that appears when you hover the mouse pointer over a cell with a hyperlink changed?

  3. #3
    Lounger
    Join Date
    Dec 2003
    Location
    San Francisco, California
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating Broken Hyperlinks (Office XP)

    Hans,

    Thank you for the reply. Yes, the macro ran to completion. There were no tooltips. The link in the cell is the full link (e.g., X:19DataPleadingscomplaint.pdf), so all that shows up when hovering the pointer is the text of the link.

    I did find that doing the following for each cell "refreshed" the link: delete, Ctrl+Z, Enter; repeat for each cell containing a link. It was very tedious to do but it relinked the link with the document on the network drive.

    My only concern now is preventing this happens again. To prevent future breakage, I unlocked all cells, locked only the cells that contains the links, and protected the sheet. I wasn't sure which of the options should be checked though so left most of them available. Users need to be able to input and format additions to the workbook, but I don't want the links to break when it's accessed remotely and saved. After people add new links, I can go in and protect those cells.

    Does that sound like a good preventative step?

    Thank you.
    Brent

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating Broken Hyperlinks (Office XP)

    If you can live with that approach, fine. (I cannot offer more help, since I don't really understand the situation)

  5. #5
    Lounger
    Join Date
    Dec 2003
    Location
    San Francisco, California
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating Broken Hyperlinks (Office XP)

    Thanks, Hans.

    I really don't want to live with this approach! [img]/forums/images/smilies/smile.gif[/img] But guess I'll have to. I apologize if I wasn't clear in my posting. The easiest way to say it is that deleting the entry in the cell, then immediately doing an undelete, somehow made the hyperlink to the network documents operate properly. That's certainly easier than having to manually edit the hyperlink.

    Since protecting the cells, there have been no more problems with broken links. So that's been a blessing.

    Hope this is helpful to some future searcher, too. This was frustrating.

    Brent

  6. #6
    Lounger
    Join Date
    Dec 2003
    Location
    San Francisco, California
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating Broken Hyperlinks (Office XP)

    Bump.

    I'm having to do this again, thanks to our IT dept. changing drives. So thought I'd bump and see if anyone has come across a way to globally change hyperlinks as set forth in post 567,085 above. Basic search/replace doesn't work, and I'm not sure if the delete/undelete method mentioned above will work in the new scenario.

    Any thoughts or ideas greatly appreciated.

    Brent

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Updating Broken Hyperlinks (Office XP)

    Brent

    Here is a little bit of VBA that could do it:

    Assume that your new path is comp-nlb-1 and is on the X drive and your links are in column A starting at A2. This code truncates the link and then adds X:comp-nlb-1 to the front and removes the old reference

    Sub changeURL()
    Dim strURL As String
    Dim strPrefix As String
    Dim intRows As Integer

    Range("A1").Select

    intRows = ActiveSheet.UsedRange.Rows.Count
    MsgBox intRows
    strURL = Selection.Hyperlinks(1).Address


    For i = 1 To intRows - 1
    ActiveCell.Offset(1, 0).Select
    Selection.Hyperlinks(1).Address = "X:comp-nlb-1" & Mid(strURL, 14, Len(strURL) - 13)
    Next i


    End Sub
    Jerry

Posting Permissions

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