VBA check if full or partial name exists in folder

[Holyfix]

Hey everyone,
I am struggling with VBA code. I have a folder with pdf files. Every file contains one or more documents. These pdf files are named accordignly to the documents inside so I may have PDF file named 8947 (one document inside) or 8468 7638 7364 (three documents inside). Also, I have an Excel sheet with column containing these numbers and i need to check if every number (PDF file) exists in the folder or not. Is there a way to write VBA code which gives me YES or NO next to these numbers in Excel ?

Thank you very much

 

[NdNoviceHlp]

Hi Holyfix and Welcome to the Board! You didn't indicate the column where the file names are located or where U want the "Yes" or "No"? Anyways, the code below assumes sheet1 column "A" has the file names and "B" is the output. You will need to adjust the folder path, sheet name and columns to suit. HTH. Dave

Private Sub tester()
Dim FSO As Object, FolDir As Object, FileNm As Object, LastRow As Integer
Dim Flag As Boolean, WS As Worksheet, Cnt As Integer
Set FSO = CreateObject("scripting.filesystemobject")
'*****change folderpath to suit
Set FolDir = FSO.GetFolder(ThisWorkbook.Path & "\Datafiles2")
Set WS = Sheets("sheet1")
With WS
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 1 To LastRow
Flag = False
For Each FileNm In FolDir.Files
If FileNm.Name Like "*" & ".pdf" Then
If InStr(FileNm.Name, CStr(WS.Cells(Cnt, "A"))) Then
WS.Cells(Cnt, "B") = "YES"
Flag = True
Exit For
End If
End If
Next FileNm
If Not Flag Then
WS.Cells(Cnt, "B") = "NO"
End If
Next Cnt
Set FolDir = Nothing
Set FSO = Nothing
End Sub

 

[Holyfix]

That is awesome, thank you very much

 

[NdNoviceHlp]

You are Welcome. Thanks for posting your outcome. Dave

 

[JEC]

Same idea

Sub jec()
 Dim i As Long
 With Sheets("sheet1")
   For i = 1 To .Range("A" & Rows.Count).End(xlUp).Row
     .Cells(i, 2) = Dir(ThisWorkbook.Path & "\" & .Cells(i, 1) & "*.pdf") <> ""
   Next
 End With
End Sub

 

[Holyfix]

Hello again, I just found out that it only seems to be working when you make a completely new excel. After I save it and run again, it gives me "Run-time error 76 (Path not found)". May I please ask you to help me with this ? Thank you

 

[JEC]

Save the new workbook first, otherwise thisworkbook.path can not be found

 

[Holyfix]

When I make new workbook, save it, reopen and use the code it still gives me "Path cannot be found". Seems like I can't use it on already existing file.

 

[JEC]

It is also important where to save it

 

[Holyfix]

Doesn't work even if I save it into the folder where rest of the PDFs are.