Want to check Hyperlink that opens PDF file saved on Server.

[Jamber89]

I have over 5,000 and continually adding hyperlinks in my excel document. These links once clicked on open a PDF file that is saved on my server. I was wondering if there was a way to check the hyperlinks to see if they are broken instead of clicking on each one. I use this formula to create my hyperlinks.

=HYPERLINK("X:\Released Drawings\"&J:J&"\"&C4&".pdf",C4)

Any help would be grateful.

Thanks,
Amber

 

[John_w]

Which cells are the hyperlink formulas in?

 

[Jamber89]

They are in Column K rows 4 - 5420.

 

[John_w]

=HYPERLINK("X:\Released Drawings\"&J:J&"\"&C4&".pdf",C4)

Does the formula need to reference the entire column J? That makes a solution using the VBA Evaluate function extremely slow because it has to evaluate all 1 million rows (Excel 2007+) for each hyperlink cell.

Could you change the formulas to one of these?

=HYPERLINK("X:\Released Drawings\"&J4&"\"&C4&".pdf",C4)

=HYPERLINK("X:\Released Drawings\"&$J$4&"\"&C4&".pdf",C4)

 

[Jamber89]

I need it to reference every place a hyperlink is listed. As of right now that is J4 thru J5420, however the list is continuously updated/added to.

I'm using Microsoft Excel 2013

 

[John_w]

My point is "J:J" in the formula references the entire column J. Is the formula you posted correct? Or is it:

K4: =HYPERLINK("X:\Released Drawings\"&J4&"\"&C4&".pdf",C4)
K5: =HYPERLINK("X:\Released Drawings\"&J5&"\"&C5&".pdf",C5)

etc. for each column K cell?

 

[Jamber89]

I believe my formula is incorrect when I look at yours.
What I's trying to do is create a link to a PDF file saved on my server.

Column J is the file location
Column C is the PDF in that file I want it to link to.

So each row will be specific to the information in that row like you explained above.

 

[John_w]

OK, assuming the formulas in K4, K5, etc. are as I posted above (you will have to change them if not), the following code checks each HYPERLINK formula in K4 to the last populated cell in column K and puts in the adjacent column L cell either "Valid" or "Invalid".

Public Sub Check_Hyperlink_FormulasX()

    Dim hyperlinkCells As Range, hyperlinkCell As Range
    Dim linkLocation As String
    
    With ActiveSheet
        Set hyperlinkCells = .Range("K4", .Cells(Rows.Count, "K").End(xlUp))
    End With
    
    For Each hyperlinkCell In hyperlinkCells
        linkLocation = EvaluateHyperlinkLocation(hyperlinkCell)
        If linkLocation <> "" Then
            If Dir(linkLocation) <> "" Then
                hyperlinkCell.Offset(0, 1).Value = "Valid"
            Else
                hyperlinkCell.Offset(0, 1).Value = "Invalid"
            End If
        End If
    Next

End Sub

Private Function EvaluateHyperlinkLocation(HyperlinkFunctionCell As Range) As String

    'Given a cell containing a HYPERLINK function, returns what the link_location parameter evaluates to
    
    Dim p1 As Long, p2 As Long
    
    EvaluateHyperlinkLocation = ""
    p1 = InStr(1, HyperlinkFunctionCell.Formula, "HYPERLINK(", vbTextCompare)
    If p1 > 0 Then
        p1 = p1 + Len("HYPERLINK(")
        p2 = InStr(p1, HyperlinkFunctionCell.Formula, ",")
        If p2 = 0 Then p2 = InStr(p1, HyperlinkFunctionCell.Formula, ")")  'optional friendly_name parameter not present
        EvaluateHyperlinkLocation = Evaluate(Mid(HyperlinkFunctionCell.Formula, p1, p2 - p1))
    End If
    
End Function

 

[Jamber89]

Thank-you Thank-you Thank-you your awesome. I only have one last question. Is there any way to have the result show up in column N since I have more data in L and M that are linked to formulas and vlookups for another sheet.

 

[John_w]

Easy. Change both instances of Offset(0, 1) to Offset(0, 3). The 3 means 3 columns to the right of column K, which is column N.

The linkLocation variable contains the folder path and file name that the hyperlink resolves to, and you could put this in the cell like this:

                hyperlinkCell.Offset(0, 3).Value = "Invalid: " & linkLocation