Results 1 to 3 of 3
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    testing for external links (Excel 2k(SP2))

    I am trying to discover if my workbook has links to any external workbooks. I can't get the sample code in the Help to work:
    <pre>aLinks = ActiveWorkbook.LinkSources(xlOLELinks)

    If Not IsEmpty(aLinks) Then
    For i = 1 To UBound(aLinks)
    MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
    Next i
    End If
    </pre>

    To test it I opened another workbook and assigned a cell in my source workbook to reference a cell in this new workbook (=[Book2]Sheet1!$A$4). When I run this code, it always returns Empty which means there aren't any links. Am I using it wrong? I also tried with type=xlExcelLinks but it still returned Empty. The help doesn't show how aLinks is declared but the text says that LinkSources returns an array so I declared aLinks as Variant.

    How can I determine if my workbook has references to other workbooks? I want to warn the user about this condition before close/save my workbook.

    Thnx, Deb <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: testing for external links (Excel 2k(SP2))

    There's some more info on external links <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=30606&Search= true&Forum=xl&Words=&Match=StarPost&Searchpage=0&L imit=10&Old=allposts&Main=30408>here</A>.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: testing for external links (Excel 2k(SP2))

    (Edited by HansV to update link to Excel MVP site)

    Deb, it should work if you use xlExcelLinks. Here's a complete example:<pre>Option Explicit
    Sub FindLinks()
    Dim strLinks As Variant
    Dim i As Integer
    strLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If IsEmpty(strLinks) Then
    MsgBox "No Links"
    Else
    For i = LBound(strLinks) To UBound(strLinks)
    MsgBox strLinks(i)
    Next i
    End If
    End Sub</pre>


    However, this doesn't really give the users the information that they need nor does it always find every link. Take a look at Bill Manville's FindLink AddIn.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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