VBA Function - Check all req files present in a Folder

[Mallesh23]

Hi Team,

I want VBA Function which will check whether all required input files present in a folder.
if files missing highlight missing files.

Below are Main Key words to check
Debtors
Creditors
Overdue
Recon
Sales Register.

Below attmpted code, not giving correct result. I am ok with any other way.

Sub Check_Files()

    Dim Filenames As String
    Dim path As String
    Filenames = "Debtors,Creditors,Overdue,Recon,Sales Register"
    path = "C:\Users\ASUS\Desktop\Reconcilation\"
    
   If Not Files_Check(path, Filenames) Then
        MsgBox "Files not found" & Chr(10) & Filenames, 16, "Files Missing in " & path
    
    End If

End Sub



Function Files_Check(ByVal path As String, ByRef Filenames As String) As Boolean
    Dim arFilename() As String
    arFilename = Split(Filenames, ",")
    Dim filesnames As String
    
    Dim i As Long
    
    Dim str As String
    str = ""
    
    Dim strFile As String
    
    For i = 0 To UBound(arFilename)
        strFile = "*" & arFilename(i) & "*.xlsx"
        Filenames = Dir(path & strFile)
        If Len(Filenames) = 0 Then
            Filenames = Filenames & "," & arFilename(i)
        End If
           
    Next i

Files_Check = CBool(Len(Filenames) = 0)
 

End Function

 

[John_w]

Try this:

Public Sub Check_Files()

    Dim filenames As String
    Dim path As String
   
    filenames = "Debtors,Creditors,Overdue,Recon,Sales Register"
    path = "C:\Users\ASUS\Desktop\Reconcilation\"
       
    If Not Files_Check(path, filenames) Then
        MsgBox "Files not found in " & path & vbLf & filenames, vbCritical, "Files Missing"
    End If

End Sub


Private Function Files_Check(ByVal path As String, ByRef filenames As String) As Boolean

    Dim arrFilenames() As String
    Dim i As Long
    Dim file As String
   
    If Right(path, 1) <> "\" Then path = path & "\"
   
    arrFilenames = Split(filenames, ",")
    filenames = ""
   
    For i = 0 To UBound(arrFilenames)
        file = Dir(path & "*" & arrFilenames(i) & "*.xlsx")
        If file = vbNullString Then
            filenames = filenames & arrFilenames(i) & ","
        End If
    Next i

    If filenames <> "" Then filenames = Left(filenames, Len(filenames) - 1)
    Files_Check = CBool(Len(filenames) = 0)

End Function

 

[Mallesh23]

Hi John,

Thanks for your help, it worked as expected.


Thanks
mg