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. #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

  3. #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"

  4. #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 )

  5. #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
  •