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?22324
I also put these files in my dropbox located here... https://www.dropbox.com/s/i3xrujc52da55df/Kwik%20Till%20To-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:clap: Thank you very much:yes oustanding work.