Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    External Links in Workbook (XP)

    Is it possible to determine if a closed workbook has external links through VBA?

    Thanks,
    John

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

    Re: External Links in Workbook (XP)

    No, the only thing you can do with a closed workbook is retrieve some document properties (using a special DLL). For everything else, you need to open the workbook.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: External Links in Workbook (XP)

    You can also also retrieve data from closed workbooks - either by using cell references or named ranges.
    Gre

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

    Re: External Links in Workbook (XP)

    Yes, but John was asking about VBA.

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: External Links in Workbook (XP)

    The only practical way to extract data from a closed workbook is by using VBA!
    Gre

  6. #6
    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: External Links in Workbook (XP)

    <hr>The only practical way to extract data from a closed workbook is by using VBA!<hr>

    If it is the only practical way, how can it be done? The way I would typically do it is to create (using VB) the external links in cells in the worksheet and then have VB access these. It has excel do the "looking" and then VB to look at excel.

    It does not get access to the formulas only the values, so it can not be used to get info on external links, which as far as I know, is only possible to do on a "closed" workbook, if you first open it (in which case it is no longer closed) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Steve

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: External Links in Workbook (XP)

    As far as the Original Poster's question is concerned, my understanding is <UL><LI>Excel stores links as the existing values at the time the workbook was last saved<LI>It also stores details regarding the link - so that the data can be recalculated (if desired)[/list]The Object Model has been exposed enough to enable extraction of the first aspect (the data), but not the second (whether or not the data is dependent on a link).

    As far as extracting data is concerned, it seems best to use the method that you mention and then use <code>.Value</code> to "freeze" the link into a hard value. The performance benefits are very substantial. A post by <!profile=LegareColeman>LegareColeman<!/profile> made me start looking at this. Using Named Ranges in the source workbook (if possible) can make the coding considerably simpler.
    Gre

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: External Links in Workbook (XP)

    Thanks for everyone's comments.

    John

Posting Permissions

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