Check for filename before placing hyperlink

[Reset]

I have a short macro that adds hyperlinks to files where the filename is the value in the first cell fro each row. It currently checks and adds if the target cell for the hyperlink is empty. But I would also like to check and make sure the filename exists in the target folder before adding the hyperlink as well. Here's the code so far:

Sub linkup()
Dim name As String
Dim c As Integer
Sheets("Sheet1").Select
c = WorksheetFunction.CountA(Columns(1))
' Filenames are in column 1, hyperlink goes is column 7
For x = 2 To c
    If Cells(x, 7) = "" Then
        name = Cells(x, 1)
        ActiveSheet.Hyperlinks.Add anchor:=Cells(x, 7), Address:= _
            "C:\Users\HumanBeing\Documents\" & name & ".pdf",  TextToDisplay:= _
            "PDS"
    End If
Next x
End Sub

Thanks.

 

[Jerry Sullivan]

You can use Dir() to test if a file exists....

    Dim sFileSpec As String
    sFileSpec = "C:\Users\HumanBeing\Documents\" & Name & ".pdf"
    If Dir(sFileSpec) <> "" Then
        MsgBox sFileSpec & " exists"
    Else
        MsgBox sFileSpec & " doesn't exist"
    End If

 

[Reset]

Worked like a charm. Thanks!