Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Can you print a list of Links?

This is a discussion on Can you print a list of Links? within the Excel Questions forums, part of the Question Forums category; Is there a way to paste a list of the Links in a sheet somewhere, so that it can be ...

  1. #1
    New Member
    Join Date
    Dec 2005
    Posts
    25

    Default Can you print a list of Links?

    Is there a way to paste a list of the Links in a sheet somewhere, so that it can be easily seen, reviewed and printed out for an audit trail?

    (i.e. an analogue to the F3-PasteList that gets you a list of the Names in a sheet.)

    Thanks for any help.

  2. #2
    Board Regular DominicB's Avatar
    Join Date
    Oct 2005
    Location
    England
    Posts
    1,569

    Default

    Good evening DD-SF

    Download and install my free add-in vi the link below. Once installed go to Ultimate > Formulae > Linked Formula Tools, ensure the drop down box is set to "Generate links reports for this workbook" and click OK.

    HTH

    DominicB
    Now available : Ultimate Add-In 2007
    Integrates directly into the Office Excel Ribbon

    The Ultimate Utilities Excel Add-In (v1.52) is now available to download.
    http://www.dom-and-lis.co.uk/
    200+ New Excel tools. 30+ New Excel functions. Free!
    New! Show Autofilter Criteria

  3. #3
    New Member
    Join Date
    Dec 2005
    Posts
    25

    Default

    Thanks - looked at your site, and it looks like a great set of tools.

    However, is there a way to get just what I am looking to do without downloading the entire package - I want to do this at a corporate location, and will not be able to use an add-in to Excel. (I will certainly look at it for my personal at-home use.)

  4. #4
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,666

    Default

    1. Go Insert>Names>Define and in the Names in Workbook box type something like "lk" - w/o the quotes

    2. In the Refers To box type:

    =LINKS()

    Click Add

    3. Back in the spreadsheet, select an empty cell and type in the formula:

    =INDEX(lk,ROWS($A$1:$A1))

    and copy the formula down.

    This will display all the external workbooks linked to (you'll get #REF errors when its run out of links).

    Hope this helps!
    Richard Schollar

    Using xl2013

  5. #5
    Board Regular DominicB's Avatar
    Join Date
    Oct 2005
    Location
    England
    Posts
    1,569

    Default

    Hey Richard

    That's a neat trick ...

    DominicB
    Now available : Ultimate Add-In 2007
    Integrates directly into the Office Excel Ribbon

    The Ultimate Utilities Excel Add-In (v1.52) is now available to download.
    http://www.dom-and-lis.co.uk/
    200+ New Excel tools. 30+ New Excel functions. Free!
    New! Show Autofilter Criteria

  6. #6
    New Member
    Join Date
    Dec 2005
    Posts
    25

    Default

    FANTASTIC! thanks a lot

  7. #7
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,804

    Default

    Neat trick, Richard. A real gem.

    It works also with =names() in the defined name. When I hid some names, I needed to re-enter the formula for it to update. The hidden & system names (print area, filter database) did not show.

    I guess there are other collections for which this works. Please post if you know some others. Cheers, Fazza

  8. #8

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default

    Hi,

    Code:
    Sub Links()
    aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(aLinks) Then
        For i = 1 To UBound(aLinks)
            MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
        Next i
    End If
    End Sub

  9. #9
    New Member
    Join Date
    Nov 2003
    Posts
    44

    Default

    1. Go Insert>Names>Define and in the Names in Workbook box type something like "lk" - w/o the quotes

    2. In the Refers To box type:

    =LINKS()

    Click Add

    3. Back in the spreadsheet, select an empty cell and type in the formula:

    =INDEX(lk,ROWS($A$1:$A1))

    and copy the formula down.

    This will display all the external workbooks linked to (you'll get #REF errors when its run out of links).

    Hope this helps!
    This is a great little function and I'd love to know the logic behind the =links() in the defined names and whether there are other similar functions that could be used??

  10. #10
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,666

    Default

    LINKS() is an XLM4 macro function which is why it can't be used directly on the sheet (but can be referenced via a defined name).

    See Microsoft's site here:

    http://support.microsoft.com/kb/128185

    for a downloadable helpfile etailing all the XLM4 functions available.
    Richard Schollar

    Using xl2013

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com