Results 1 to 5 of 5
  1. #1
    Carlwarren
    Guest

    Embedded Links

    All

    I am about to attempt to migrate 28gb of finance data from netware to a New NT structure I know that some drive letters and paths are going to change. Is there any way of scanning the excel files on the server to check for embedded links
    Just knowing it had links would be a start knowing what they were would be a god send.
    Theres about 40000 of them btw If any one has attempted simular any ideas would also be helpfull

    Thanks
    Carl

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Embedded Links

    Carl,

    28GB!?!?! [img]/w3timages/icons/nuts.gif[/img] Wow...

    I can't think of any reason for finance data to have 'links' embedded... Most of my coding experience (not that i've been doing it for any period of time) has taught me that references are made within the worksheet/workbook.

    I would think that any instance of reference would be rare, and even if there were a few, perhaps your helpdesk/programmers can sort out the issues as they arise.

    When in doubt, back it up... perhaps you can beef up a workstation (still with Novell) and leave copies of critical data there, so that, if needed, users will still have access to them in thier prior form. (I'm sure you've already considered this in your undertaking...) Again, as it is financial data, I don't forsee a problem, but then people do strange things to excel... [img]/w3timages/icons/wink.gif[/img]

    That's my 2cents!

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Embedded Links

    Carl,
    Assuming we're talking about links to other workbooks, then you could use the Workbook.Linksources method within a loop that opened each file, checked the links and then closed it again. You could also get it to write the filename and linkname to a new workbook. Wouldn't be very quick looking through 40000 workbooks, but probably better than doing it manually![img]/w3timages/icons/grin.gif[/img]
    I've never personally done this but I guess you'd use Dir to return an array of the workbooks in a given directory, then do the checking loop.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Embedded Links

    Sounds like one big chore... The good and bad news is that:
    1. Since this is financial data, there's a good probability that you have lots of external links. A standard approach is to have a YTD roll-up that links to individual monthly workbooks etc.
    2. IF the interlinked workbooks reside in the same subdirectory, changing the path won't affect them since Excel doesn't save the 'fully qualified' path name in this instance. At least it recognizes that the linked workbook is in the same subdirectory as the linking workbook and will attempt to find the linked workbook therein. (You can copy a group of linked workbooks that are all interconnected to each other within the same subdirectory to other subdirectory and, when opened, they will still refer to each other)
    3. IF, however, the interlinked workbooks reside in different subdirectories, the fully qualified path is saved and when you open the workbook in the new environment, they won't be able to find the workbooks they're linked to.

    If you have a user from the financial area that's familiar with how these workbooks might be interconnected, it might pay to get some insight on how they're organized. Worse case is that the individual users will have to go through the Edit-Links-Change Source dialog as they begin using the workbooks in the new environment (unless you can find a pattern that will allow you to identify just the workbooks with links to other workbooks that are in subdirectories different than their own).

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Embedded Links

    I Stand Corrected!
    Attached Images Attached Images
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

Posting Permissions

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