VBA in Excel to search for PDF's and report if exist yes/No

[Banger978]

Hello, I have this code below that searches our server for existing PDF files and reports back in a column Yes or no. It works fine, but not for all subfolders. Can this be modified instead of searching just X:\PDF Drawings\55-TILL PDF
Can it be this folder X:\PDF Drawings\ and all 15 subfolders?Kwik Till To-Do.xlsm
I also put these files in my dropbox located here... https://www.dropbox.com/s/i3xrujc52d...To-Do.zip?dl=0
I included a few pdf's, and also a map of the network drive.
the file capture.jpg shows the map of the X:\PDF Drawings\ and all subfolders.

[Bob Phillips]

Try this

Sub CheckIfFileExists()
Const LPath As String = "C:\PDF Drawings\"
Const LExtension As String = ".pdf"
Dim FSO As Object
Dim folder As Object
Dim filefound As Boolean
Dim lastrow As Long
Dim i As Long
    
    Set FSO = CreateObject("Scripting.filesystemobject")

    With ActiveSheet
        
        .Range("D1") = "Assy Drawing Finished Yes/No"
   
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 1 To lastrow

            filefound = False
            Set folder = FSO.GetFolder(LPath)
            If Dir(LPath & .Cells(i, "A").Value & LExtension, vbNormal) = vbNullString Then
                
                For Each folder In folder.subfolders
                
                    If Dir(LPath & folder & Application.PathSeparator & .Cells(i, "A").Value & LExtension, vbNormal) <> vbNullString Then
                        
                        filefound = True
                        Exit For
                    End If
                Next folder
            Else
            
                filefound = True
            End If
            
            'Place "No" in column E if the file does NOT exist, "Yes" if it does
            If filefound Then
            
                .Cells(i, "D").Value = "No"
            
            Else
                .Cells(i, "D").Value = "Yes"
            End If
        Next i
    End With
    
    Set folder = Nothing
    Set FSO = Nothing
End Sub

[Banger978]

Thanks for your fast reply.

I changed C:\PDF Drawings to X:\PDF Drawings, since that is our network folder where all PDF's reside.
Then I get this error.
Run Time error '52'
Bad file name or number

When I click debug

it highlights this line yellow
If Dir(LPath & folder & Application.PathSeparator & .Cells(i, "A").Value & LExtension, vbNormal) <> vbNullString Then

Any ideas?

[Bob Phillips]

This should be better

Sub CheckIfFileExists()
Const LPath As String = "X:\PDF Drawings\"
Const LExtension As String = ".pdf"
Dim FSO As Object
Dim folder As Object
Dim filefound As Boolean
Dim lastrow As Long
Dim i As Long
    
    Set FSO = CreateObject("Scripting.filesystemobject")

    With ActiveSheet
        
        .Range("D1") = "Assy Drawing Finished Yes/No"
   
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 1 To lastrow

            filefound = False
            Set folder = FSO.GetFolder(LPath)
            If Dir(LPath & .Cells(i, "A").Value & LExtension, vbNormal) = vbNullString Then
                
                For Each folder In folder.subfolders
                
                    If Dir(folder & Application.PathSeparator & .Cells(i, "A").Value & LExtension, vbNormal) <> vbNullString Then
                        
                        filefound = True
                        Exit For
                    End If
                Next folder
            Else
            
                filefound = True
            End If
            
            'Place "No" in column E if the file does NOT exist, "Yes" if it does
            If filefound Then
            
                .Cells(i, "D").Value = "Yes"
            
            Else
                .Cells(i, "D").Value = "No"
            End If
        Next i
    End With
    
    Set folder = Nothing
    Set FSO = Nothing
End Sub

[Banger978]

That did it, it works perfectly now Thank you very much oustanding work.