+ Reply to Thread
Results 1 to 4 of 4

Can i change multiple hyperlinks at once

  1. #1
    Magser
    Guest

    Can i change multiple hyperlinks at once

    I have a large database of files that are hyperlinked in a File Register
    created in excel. All of these files have now been moved to a different
    drive, but the folder names etc are the same. Is there a quick way to change
    the path of the hyperlink, as to do each one individually would take weeks.

  2. #2
    Mladen_Dj
    Guest

    Re: Can i change multiple hyperlinks at once


    "Magser" <[email protected]> wrote in message
    news:[email protected]...
    >I have a large database of files that are hyperlinked in a File Register
    > created in excel. All of these files have now been moved to a different
    > drive, but the folder names etc are the same. Is there a quick way to
    > change
    > the path of the hyperlink, as to do each one individually would take
    > weeks.


    Just go to Edit->Replace, and choose part of path which was changed (for
    example C:\), and write it in "Find what:" box, than put new patcial path in
    "Replace with" box (for example D:\). unselect "Match entire cell
    contents:" if it is selected, and click "Replace all".




  3. #3
    vezerid
    Guest

    Re: Can i change multiple hyperlinks at once

    The following VBA function will replace the drive names as you desire
    after you change the words inside the quotes to the desired ones.

    Sub ChangeHyperlinks()
    Dim h As Hyperlink
    Dim oldDr As String, newDr As String

    oldDr = "http"
    newDr = "ftp"
    For Each h In Sheets("Sheet1").Hyperlinks
    h.Address = newDr & Mid(h.Address, Len(oldDr) + 1, Len(h.Address))
    Next h
    End Sub

    HTH
    Kostis Vezerides


  4. #4
    Mladen_Dj
    Guest

    Re: Can i change multiple hyperlinks at once


    "Mladen_Dj" <[email protected]> wrote in message
    news:[email protected]...
    >
    > "Magser" <[email protected]> wrote in message
    > news:[email protected]...
    >>I have a large database of files that are hyperlinked in a File Register
    >> created in excel. All of these files have now been moved to a different
    >> drive, but the folder names etc are the same. Is there a quick way to
    >> change
    >> the path of the hyperlink, as to do each one individually would take
    >> weeks.

    >
    > Just go to Edit->Replace, and choose part of path which was changed (for
    > example C:\), and write it in "Find what:" box, than put new patcial path
    > in "Replace with" box (for example D:\). unselect "Match entire cell
    > contents:" if it is selected, and click "Replace all".
    >

    Huh, its seems not work , because excel don't change path but only text in
    the cell. Then you can use formula, for example if hyperlinks are in column
    A, put in B1 "=HYPERLINK("D"&RIGHT(A1,LEN(A1)-1))" where D is drive letter
    which is changed, and then copy formula to the end of file list. Sorry for
    the previous wrong tip.



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1