Results 1 to 5 of 5
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi All!

    I'm looking for a way (in VBA) of telling if an excel file has links to other spreadsheets. Basically, I want to put this into a vba function and then do something like ...

    Code:
    if doesThisFileHaveExternalLinks then
        msgbox "external links detected ... this is bad"
    endif
    Oh - while I am here - I guess I will shortly be looking for a way of listing these external links .

    Ruff
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    XL2003 has a Link Sources method that returns an array of links in a workbook.
    I haven't tested it, but I suspect it may not find every type of link possible.
    For something quick and dirty you can do a UsedRange search for a bracket... "["
    '--
    Jim Cone
    Portland, Oregon
    XL add-ins

  4. #3
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is what I ended up using
    Code:
    Public Function isHaveLinkedCells() As Boolean
    Dim lCell As Variant
    Dim lCells As Range
    Dim lSheet As Object
    Dim lLinkTest As Integer
    
    ' remember the current location
    ' StoreCurrent  <- this is a tiny function that stores the name of the book, tab and cell
    isHaveLinkedCells = False
    
    For Each lSheet In Sheets
        lSheet.Activate
        
        ' Error trapping if no formulacells found
        On Error Resume Next
        
        ' Select only formula cells
        Set lCells = Nothing
        Set lCells = Cells.SpecialCells(xlFormulas)
        If Not lCells Is Nothing Then
            ' Loop every formulacell in sheet
            For Each lCell In lCells
                lLinkTest = 0 ' this is required because the next line can sometimes throw errors
                lLinkTest = InStr(1, lCell.Value, ".xls]")
                If lLinkTest > 0 Then
                    isHaveLinkedCells = True
                    GoTo TheEnd
                End If
            Next
        End If
    Next
    
    TheEnd:
    'ReturnToCurrent  ' you can guess what this does
    On Error GoTo 0
    End Function
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  5. #4
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Suggest you change...
    lLinkTest = InStr(1, lCell.Value, ".xls]")

    To...
    lLinkTest = InStr(1, lCell.Formula, ".xls]")

    Also that check will always return 0 for a xl2007 workbook extension.
    You might want to just check for both a bracket and an exclamation point.

    Your check will be quick but not complete as you can also have links in charts, shapes, hyperlinks, names, pivot tables and ?
    '--
    Jim Cone
    Portland, Oregon USA
    ( Special Sort add-in )

  6. #5
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Jim Cone View Post
    lLinkTest = InStr(1, lCell.Formula, ".xls]")
    Yeah - sloppy testing on my behalf. Just found out the above fix during a live demo ... sigh.
    Also that check will always return 0 for a xl2007 workbook extension.
    You might want to just check for both a bracket and an exclamation point.

    Your check will be quick but not complete as you can also have links in charts, shapes, hyperlinks, names, pivot tables and ?
    '--
    Jim Cone
    Portland, Oregon USA
    ( Special Sort add-in )
    correct - but good enough for what I am after as I am only interested in links in cells.

    Thanks for your comments.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

Posting Permissions

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