Results 1 to 12 of 12
  1. #1
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    automatically updating links (2003)

    Hello all,

    We have a lot of Excel workbooks that contains links to other workbooks. But our IT department has the idea to change the global folderstructure of the networkdrive. This will mean that a workbook from the folder V:dep01 will move to the folder V:depsdep01.
    When they start moving folders and files to other levels all the workbooks will lose the link between them.
    Is there a way to automatically check every workbook in the folders and correct the links?

    Tnaks in advance for your answers
    Greetings,

    Patrick Schouten
    (The Netherlands)

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

    Re: automatically updating links (2003)

    As long as the relative location of the workbooks remains the same, Excel will gracefully resolve the links. For example, say you have a workbook V:dep01managementsummary.xls that refers to another workbook V:dep01surgerypatients.xls. After the change, you'll have V:depsdep01managementsummary.xls and V:depsdep01surgerypatients.xls. Since the relative position of the workbooks has remained the same, the links should still work, without any work on your side.
    If, however, the second workbook is moved to V:depsdep01surgerycardiacpatients.xls, the links would break because the relative position has changed by the introduction of an extra level for only one of the workbooks.

  3. #3
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: automatically updating links (2003)

    Thanks Hans,

    Luckely 70% of the movements will be relative, but 30% will move to other locations.
    Is it possible for those 30% to use a macro to correct the links?
    Greetings,

    Patrick Schouten
    (The Netherlands)

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

    Re: automatically updating links (2003)

    No doubt it's possible, but is there a fixed pattern to the changes, or will you have to specify the new path for individual files? It might help to prepare a worksheet that lists the changes.

  5. #5
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: automatically updating links (2003)

    Hans,

    Yes, there is a pattern to te movement. It is possible to create a sheet with old en new locations just by foldernames.
    Greetings,

    Patrick Schouten
    (The Netherlands)

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

    Re: automatically updating links (2003)

    Let's say you create a sheet named List with the old path in column A and the corresponding new path in column B, starting in row 2.
    Put the attached macro in a module in the workbook containing this sheet.
    The macro will loop through all workbooks in the specified folder and its subfolders, and replace each of the specified strings.

    *** WARNING: test the macro carefully on a test folder with copies of workbooks before running it on your real workbooks! ***

    If the number of folders and workbooks to be processed is large, I'd recommend running the macro separately in different folders (by changing the line .LookIn = "V:depsdep01") instead of trying to do it all at once from a "root" folder - VBA might well choke if you try that.
    Attached Files Attached Files

  7. #7
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: automatically updating links (2003)

    Cool Hans,

    Thank you very much for your effort.
    I will give it a test.
    Greetings,

    Patrick Schouten
    (The Netherlands)

  8. #8
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: automatically updating links (2003)

    Hello Hans,

    It seems to work correctly after I changed to following lines:
    <pre>strOld = wshList.Range("A" & r)
    strNew = wshList.Range("B" & r)</pre>


    You used the brackets in your code, but then you are replacing the file name and not the foldername
    Thanks again for your help
    Greetings,

    Patrick Schouten
    (The Netherlands)

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

    Re: automatically updating links (2003)

    Sorry about that, it was just air code. Glad you were able to correct it yourself! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  10. #10
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: automatically updating links (2003)

    HAns,

    Just another question about this.
    Is it possible when opening workbooks with a for/next loop to check when opening a workbook if that workbook has external links at al?
    If you look at the Excel menu ,edit, links (I'm not sure about the correct english menu names) you see an overview of the links used by that workbook.
    Is it possible to read those links in a variable?
    Greetings,

    Patrick Schouten
    (The Netherlands)

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

    Re: automatically updating links (2003)

    The LinkSources property of a workbook is either empty (if there are no links), or an array of strings describing the links. You can use code like this:

    Dim arrLinks
    Dim i As Integer
    arrLinks = ActiveWorkbook.LinkSources
    If IsEmpty(arrLinks) Then
    Debug.Print "No links!"
    Else
    For i = LBound(arrLinks) To UBound(arrLinks)
    ' Do something with the link string
    Debug.Print arrLinks(i)
    Next i
    Erase arrLinks
    End If

  12. #12
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: automatically updating links (2003)

    Again Hans,
    Thanks for sharing you knowledge
    Greetings,

    Patrick Schouten
    (The Netherlands)

Posting Permissions

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