Hi Garbagh,

Thanks for reaching out. I understand that you have a large spreadsheet with hyperlinks to files on your system and you need a way to check that all the links work without going through and clicking them one at a time. I have a solution that might work for you.

You can use a

Formula:

VBA macro 


to cycle through the hyperlinks and check whether each reference file exists. The macro will generate a list of bad targets in a new sheet. Here are the steps:

1. Open your Excel file and press ALT + F11 to open the VBA editor.
2. In the VBA editor, go to the ThisWorkbook module and paste the following code:
   
   
   Formula:

   Sub ChkHypLnks()
    Dim wksHypLnks, wksBadHypLnks As Worksheet
    Dim curHypLnk As Hyperlink
    Dim curFile As String
    Dim iBadHypLnks As Integer
    
    Set wksHypLnks = ActiveSheet
    Set wksBadHypLnks = ThisWorkbook.Worksheets.Add
    
    wksBadHypLnks.Name = "BadHypLnks" _
        & Right(wksBadHypLnks.Name, Len(wksBadHypLnks.Name) - 5)
       
    For Each curHypLnk In wksHypLnks.Hyperlinks
        If Dir(curHypLnk.Address) = "" Then
            iBadHypLnks = iBadHypLnks + 1
            wksBadHypLnks.Cells(iBadHypLnks, 1) = curHypLnk.Address
        End If
    Next curHypLnk
    Application.DisplayAlerts = False
    If iBadHypLnks < 1 Then wksBadHypLnks.Delete
    Application.DisplayAlerts = True
End Sub 

3. Save the VBA code and close the editor.
4. Go back to your Excel sheet and run the macro ChkHypLnks from the sheet where the hyperlinks are.
5. The macro will generate a list of bad targets in a new sheet.

Please note that this macro will only check if the file exists, not if the hyperlink is valid. If the hyperlink is incorrect, it will still show up as a bad target.