Microsoft Excel: Finds all files in a specified directory with an optional specified criteria (extension, name)

ListFilesInDirectory.bas

Sub ListFilesInDirectory()

  Dim fsFile, sFilename, sPath As String
  Dim i As Integer
  
  i = 2
  
  'Set file attributes
  sPath = "C:\"
  sFilename = "*.pdf"
  
  'Checks if directory empty
  If Dir(sPath, vbDirectory) <> "" Then
    
    fsFile = Dir(sPath & sFilename)
    
    'Iterates through each file in the directory
    Do While fsFile <> ""
      Cells(i, 1).Value = fsFile
      i = i + 1
      fsFile = Dir
    Loop
    
    'Whilst directory wasn't empty, this determines if files matching criteria were found or not
    If (i = 2) Then
      MsgBox "No files like " & sFilename & " found"
    End If

  Else
  
    'No files found, display message
    MsgBox "No files Found"
    
  End If

End Sub