Find links to external files

[ben.burgess]

Hello gracious Mr Excel community,

It's been a little while since I've posted on here and I'm hoping the brilliant minds here can help. I've been tasked with a project (as we're moving to a cloud server) to list all files with external links so these can be manually updated when this happens as there will no longer be a network drive. Unfortunately, looking at the drive, we have over 20k excel files. That would take waaay too long to go through manually. What i would like to achieve is to create a list of those files with external links but the problem we have is a lot of files have protected sheets and so this causes errors when running the code. Ultimately I have a list of files on a worksheet with the file path and name separated

I would like column C to show either
"Cannot check for links as sheets are protected" for those files where sheets cannot be accessed,
"External links found" where there is an external link (it doesn't matter of there's 1 or 20 externally linked files)
"No links found" for files without any external links
"Error. Could not open sheet" if the workbook cannot be accessed.

I've adapted some code i have sourced from here but I'm having trouble with the error handling and just can't seem to find the right combination of error handling to work this out and it's doing my head in but I'm running out of time to get a working list together.

Sub CheckLinks()

'Public variable named for; TmpBook As Workbook
Dim UsedRange As Variant
Dim Location As String
Dim FileName As String
Dim FullPath As String

Set ThisBook = ActiveWorkbook
Set ThisSheet = ActiveSheet
UsedRange = ActiveSheet.UsedRange.Rows.Count

    Dim ActiveRow As Variant
    For ActiveRow = 2 To 200 'UsedRange
    Application.StatusBar = "Processing ... " & ActiveRow & " of " & UsedRange
        Links = ""
        FullPath = Range("B" & ActiveRow).Value & Range("A" & ActiveRow).Value
        
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Application.EnableEvents = False
            
            On Error GoTo Skip
            Workbooks.Open (FullPath)
            Set TmpBook = ActiveWorkbook
            totalSheets = Application.Sheets.Count
            cSheet = 0
                
                'Runs find function for [ signifying external link
                Dim xSheet As Worksheet
                Dim xRg As Range
                            
                For Each xSheet In Worksheets
                On Error GoTo lblSkipSheet
                    Set xRg = xSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
                    If xRg Is Nothing Then
                            GoTo lblXsheet
                    Else
                            On Error GoTo lblXsheet
                        Dim xCell As Range
                        For Each xCell In xRg
                            If InStr(1, xCell.Formula, "[") > 0 Then
                                Links = "External links found"
                                GoTo LblCloseBook
                           End If
                        Next xCell
                    End If
                GoTo lblXsheet

lblSkipSheet: 'handler for error in accessing sheet formulas. usually comes up when a sheet is protected
                cSheet = cSheet + 1
                If cSheet = totalSheets Then
                    Links = "Cannot check for links as sheets are protected"
                    GoTo LblCloseBook
                End If
                Resume Next
lblXsheet: 'goto next worksheet in book
                Next xSheet
LblCloseBook: 'immediate handler for closing temp workbook
                If Links = "" Then
                    Links = "No links found"
                End If
            ActiveWorkbook.Close
            GoTo Update

Skip: 'handler if workbook cannot open
            Links = "Error. Could not open sheet"
            On Error GoTo -1
            GoTo Update

Update: 'runs script to update source workbook with detail if external links found in temp file
            Application.DisplayAlerts = True
            Application.EnableEvents = True
            Application.ScreenUpdating = True
            
            Range("C" & ActiveRow).Value = Links
            
            'scrolls to active cell so user can see macro updating
            If ActiveRow > 5 Then
                Application.Goto Range("A" & ActiveRow - 5), True
            Else
                Application.Goto Range("A" & ActiveRow), True
            End If
      Next ActiveRow
         
'reactivates excel standard defaults
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.StatusBar = ""
End Sub

Sorry it's a lengthy bit of code
Thank you!

 

[GlennUK]

Why on Earth would you investigate every formula of every sheet when the workbook method .LinkSources(xlExcelLinks) generates an object of all links?

 

[ben.burgess]

Thank you, that would be great. I didn't find this method when I was searching for how to get external links. Much appreciated.