Actually, you don't need to open a file to check if it exists... Run the
macro below whenever your hyperlinks or their targets change:

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

Just copy the code and paste it in the Thisworkbook module on the VBA
editor, then run the macro ChkHypLnks from the sheet where the hyperlinks
are. It will generate a list of bad targets in a new sheet. You will find out
that it is blazing fast.

Best,

Rafael

"Garbagh" wrote:

I have had trouble getting the macro to recognise the opening of the
hyperlink as this is a mouse function.

I appreciate this may be difficult/impossible but i would really like a way
of checking the links without fully opening the file attached .Excel carries
out a limited version of this when warning of opening hyperlinks (the error
message for asking whether you want to open the hyperlink is different to the
one telling you that the address is invalid)

Opening all of the 3000+ files at once would probably kill my PC.

the files the hyperlink refers to are dynamic and change week to week (from
a library of over 40,000)

"Rafael Guerreiro Osorio" wrote:

I think the only way to do this is to write a VBA macro to cycle through the
hyperlinks and check whether the each reference file exist.

Best, Rafael

"Garbagh" wrote:

I have a large spreadsheet with hyperlinks to files on my system.

I need a way to check that all the links work without going through and
clicking them one at a time (for 3000+ hyperlinks this would be extremely
impractical)

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.

I need something similar and didn't think it was best to create a new thread...

I need a macro to check hyperlinks as well but to delete the entire line the hyperlink was on if it returns a 404 response. I have over 45k hyperlinks to check if the image locations are valid. Could someone please help.