determine if a file exists in a directory by its partial name

[amineact]

Hello
In a production masterbook, i have 10 excel spreadsheets. They have a specific name , like File1_04052022
I'm looking to build a macro that lets me know if THE 10 files exists even if i change the dates in case of future implementations. meaning it should be able to detect File1_05052022
I set up a loop between an array containing the partial names and a loop going through the files in the directory
I tried using Instr but it seems it doesn't serve it's purpose.
Here's the code i used:

Sub tester()
Dim FSO As Object
Dim fldr As Object
Dim folder As String
Dim filesnames As Variant
Dim c As Integer
folder = "Inputs taux"
filesnames = Array("File1", "File2","File3","File4","File5","File7","File9","File10")
c = 0
Set FSO = CreateObject("Scripting.FileSystemObject")
Set fldr = FSO.GetFolder("C\Myenv\")


For Each var In filesnames
 For Each File In fldr.Files
    If InStr(File.Name, var) > 0 Then
     c = c + 1
    End If
 Next
 If c = 0 Then
  MsgBox (" There is a missing file")
  Exit Sub
 Else
 End If
Next var
If c = 10 Then
 Range("J2").Value = "OK"
End If
 


End Sub

Appreciate any help i can get
Thanks in advance.

 

[Domenic]

Try moving your If statements after both your For Each/Next loops.

Also, I would suggest that you replace . . .

If c =10 Then

with

If c = UBound(filesnames) + 1 Then

. . . so that you won't have worry about changing that line whenever you add and/or delete file names from the array filesnames. So maybe something like this . . .

For Each Var In filesnames
    For Each file In fldr.Files
        If InStr(file.Name, Var) > 0 Then
            c = c + 1
        End If
    Next
Next Var

If c = UBound(filesnames) + 1 Then
    Range("J2").Value = "OK"
ElseIf c > 0 Then
    MsgBox "One or more files are missing"
Else
    MsgBox "No files were found"
End If

Hope this helps!

 

[amineact]

Hello Domenic
A superb solution. I tried a solution of myslef which was non as elegant and generalized as yours.
Thanks

 

[Domenic]

That's great, I'm glad I could help.

And thanks for your feedback.

Cheers!