Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Newfoundland
    Posts
    41
    Thanks
    4
    Thanked 0 Times in 0 Posts

    List Linked Files

    Is there a simple way to show the list of linked files somewhere in a file?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: List Linked Files

    From the menu, select Edit then Links...
    If "Links..." is grayed out, then there are no external links.
    The above just tells you which external files are referenced - it doesn't tell you which cells in the workbook actually have the links.

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Newfoundland
    Posts
    41
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: List Linked Files

    Thanks for the reply. I didn't make myself clear the first time. I realize that I can view the linked files where you suggest, but what I'm after is to print a cover page for my model which lists the files which are linked to it. Any suggestions?? TIA

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: List Linked Files

    Something like the following should work:

    Sub ShowLinks()
    Dim vntLinks As Variant
    Dim i As Integer

    vntLinks = ThisWorkbook.LinkSources

    For i = 1 To UBound(vntLinks)
    Sheets("Sheet1").Cells(i, 1) = vntLinks(i)
    Next i
    End Sub

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

    Re: List Linked Files

    I do precisely this same function. A non-VBA approach would be:

    In cell A1 enter =CELL("filename") (should display the workbook path/name)

    Define a name, let's say "Show_Links" with its reference as =LINKS(A1,1)

    Select a vertical range of cells that is large enough to hold entries for the number of files you expect to be linked

    Enter =TRANSPOSE(Show_Links)in that range -- you should get back the filenames of all linked files or #N/A if no link exists.

    I like to pretty this up a bit by putting the 'Transpose return' in a hidden column. I then format a column wide enough to hold long path/filenames and fill it with IF statements like: =IF (ISNA(A2),"No Link Exists",A2). Sometimes, the return gets confused and fills the entire return range with the same name if only one file is linked. I add a check to the IF statement if the cell is not #N/A, to see if it was equal to the one above it and display the "No Link Exists" if it is.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: List Linked Files

    Where is the =LINKS() function? Does it come with an add-in that you have loaded?

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

    Re: List Linked Files

    It's a 'left-over' from Excel4. You can use some of these functions by defining them as Names like in my example.

  8. #8
    Lounger
    Join Date
    Jan 2001
    Location
    Newfoundland
    Posts
    41
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: List Linked Files

    This is exactly the functionality I'm looking for. And, needless to say, I'd prefer a non-VBA solution. However, I tried typing =LINKS(A1,1) and received the message "That function is not valid". I'm using Excel 97 SR2. Is it possible that I need an add-in, or is this only available in Excel 2000. Thanks.

  9. #9
    Lounger
    Join Date
    Jan 2001
    Location
    Newfoundland
    Posts
    41
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: List Linked Files

    Worked like a charm! Thank you.

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

    Re: List Linked Files

    You can use just =LINKS() as the reference for your Name -- you need to have saved the workbook at some point.

    I neglected to mention that the Transpose formula needs to be 'array entered' in your target range by selecting the range and using Cntrl-Shift-Enter when entering the formula.

  11. #11
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: List Linked Files

    Thanks. It works for me (Excel 2000). I had guessed you meant an array formula, but the process only worked when I changed =LINKS(A1,1) to =LINKS(). Before that they all showed as #N/A.

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

    Re: List Linked Files

    According to the HELP file, LINKS requires the full path/workbook name as its first parameter (defaults to current workbook). As I recall, the first time I tried this many moons ago, I had 'volatility' problems, in that, if the user added a new link, it wouldn't show up in the list until the workbook was closed then re-opened. I think I added the CELLS("filename") approach and referenced that cell in the =LINKS reference to get around that somehow. I think the LINKS HELP may be wrong in that only the workbook name without the path seems to work. At any rate, I now use a short sub in the Worksheet_Activate event (I have one sheet which is only used for this display) to generate the workbook name in a cell and it makes the whole thing volatile.

Posting Permissions

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