Checking a Directory for a new file name that contains specific text

[Stabi]

Hi, Long time reader, first time poster...

I have a table with with a list of values that represent serial numbers. Im looking for some VBA code that will check a specific directory to see if someone has added a file where the filename contains one of the serial numbers and updates the value in an adjacent cell (from "No" to the filename for example).

These files are being created and save to a directory over time so from time to time I would need to open this workbook and update. Could possible assign a macro to a button to run a check.

Im thinking something that uses a loop to run down the list and check each serial number and return a response the cell offset by 1 column.

Col A	Col B
Serial Number	File exists in DIR	comment
185029	OPT_185029_20190622.xlsx	filename could be something like: OPT_185029_20190622.xlsx or 185029-DW.xlsx
185030	No
185031	No

<tbody>
</tbody>

Hopefully someone has some ideas

Thanks

 

[theBardd]

Try this

Public Sub test()
    MsgBox CheckFileExists("C:\Users\Bob\Documents\Projects\_8 Community\Forums\VBAExpress", "65382")
End Sub
Public Function CheckFileExists(ByVal Path As String, ByVal FileId As String) As Boolean
    CheckFileExists = Dir(Path & Application.PathSeparator & "*" & FileId & "*") <> vbNullString
End Function