Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing Links (Office 2000)

    Hello,

    This question could apply to Word as well.
    I have an Excel spreadsheet or a Word document that consists mostly links to video / movie files. Itís like an index. The video files are in the same directory as the excel file. When I burn the directory to a CD/DVD, the excel spreadsheet still points to the directory on my hard drive which makes it useless. Sometimes I like to change the Excel / Word file to a .pdf file but the result is the same. I think however if the excel spreadsheet was changed the pdf would change as well.

    1. Is there a way that I can have excel point to the file in itís own directory?
    2. I have many links so is there a way to change them all at once?
    3. Is there a way that I can see what the links are other than hovering the mouse over each one? In other words, have them listed in another column?

    Thank you!
    Louise

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Changing Links (Office 2000)

    1) If the links are created relative the path for the active file they will stay relative. SO if the the folder that contains all are moved, the links remain active. If you create the hyperlink based on a starting location it will keep this link even if the file is moved elsewhere. If the link is relative and the file is moved but the referrenced file is not the link becomes "broken"

    2) You can loop through them all making a change. For example something like this:

    <pre>Option Explicit
    Sub FixHyperlink()
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim hlink As Hyperlink
    Dim sOld As String
    Dim sNew As String

    Set wkb = ActiveWorkbook
    Set wks = ActiveSheet
    sOld = "OldPathFolder1Folder2"
    sNew = "NewpathFolder1Folder2Folder3"

    For Each hlink In wks.Hyperlinks
    hlink.Address = Application.WorksheetFunction. _
    Substitute(hlink.Address, sOld, sNew)
    Next hlink
    End Sub</pre>


    3) Here is a macro which summarizes the links in a workbook. It places the summary in a newly created worksheet in a newly created workbook.

    <pre>Option Explicit
    Sub ListHyperlinks()
    Dim wks As Worksheet
    Dim wkb As Workbook
    Dim wksNew As Worksheet
    Dim wkbNew As Workbook
    Dim HL As Hyperlink
    Dim lRow As Long

    Set wkb = ActiveWorkbook
    Set wkbNew = Workbooks.Add
    Set wksNew = wkbNew.Worksheets(1)

    With wksNew
    lRow = 1
    .Cells(lRow, 1) = "Worksheet"
    .Cells(lRow, 2) = "Cell"
    .Cells(lRow, 3) = "Address"
    .Cells(lRow, 4) = "SubAddress"
    .Cells(lRow, 5) = "Display"
    For Each wks In wkb.Worksheets
    For Each HL In wks.Hyperlinks
    lRow = lRow + 1
    .Cells(lRow, 1) = wks.Name
    .Cells(lRow, 2) = HL.Range.Address
    .Cells(lRow, 3) = HL.Address
    .Cells(lRow, 4) = HL.SubAddress
    .Cells(lRow, 5) = HL.TextToDisplay
    Next
    Next
    .Cells.EntireColumn.AutoFit
    End With

    MsgBox "Done"

    Set wks = Nothing
    Set wkb = Nothing
    Set wksNew = Nothing
    Set wkbNew = Nothing
    End Sub</pre>


    Steve

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

    Re: Changing Links (Office 2000)

    This is for Excel only:

    Put the following formula in a cell, for example in B1:
    <code>
    =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)
    </code>
    This formula returns the path of the workbook.
    Put the names of the files you want to link to in a column, say in column A starting at A2. Don't include the path.
    Enter the following formula in B2:
    <code>
    =HYPERLINK($B$1&A2,A2)
    </code>
    and fill down as far as needed. Column B will contain hyperlinks to the files, and they will always point to the folder that contains the workbook.

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Links (Office 2000)

    Hi Steve,

    Looks a little complicated to me <img src=/S/alien.gif border=0 alt=alien width=14 height=15> <img src=/S/alien.gif border=0 alt=alien width=14 height=15> but will check it out. Thank you very much!
    Louise

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Links (Office 2000)

    Thank you Hans! Should be a time saver.
    Louise

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

    Re: Changing Links (Office 2000)

    If I move an Excel workbook or a Word document with hyperlinks together with the linked files to another folder, the hyperlinks still work correctly.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Links (Office 2000)

    Hi Hans,

    I think you're right. I was baffled this morning when I tried to run the CD at work. I know that I have done this many times before and it always worked. So while I was thinking about it at lunch, I went over in my mind what I did.

    When using my Menu program to build the menu for the CD, I burned the CD using the directory that "it", the software, had created and not the "working" one that I had tested. That must be it. Now I'll try it again when I get home and I'm sure everything will be fine. That's what I get for working so late at night and trying to hurry.
    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    Thanks again Hans,
    Louise

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Changing Links (Office 2000)

    Hi Louise,

    For instructions on how to set up a relative hyperlink path is Word, see <post#=670027>post 670027</post#>
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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