CODEWITHSUNDEEP
excelfunctionvbadir
Finch042
Finch042

Reputation: 307

VBA, Dir Function, Filtering to avoid files

So basically the intent is the use a VBA dir function in a directory, get the file names in that directory, but avoid/filter out getting a certain extension. e.g., I'd like to get all the files in a directory that aren't "*.txt" or whatever. I would strongly prefer to use the dir function as opposed to some other solution, as it seems to be exceedingly more efficient/quick at getting file lists vs anything else I've tried.

For example, something like Dir("Path" & "" & <> ".txt"). But you know... something that works. haha.

I could certainly just have a check after the fact to skip over anything with a .txt extension (split a string or whatever), but I was wondering if there was something more elegant/resource efficient that I could use with the syntax of a Dir function to avoid files of a certain extension.

Thanks in advance!

Upvotes: 1

Views: 9609

Answers (1)

stenci
stenci

Reputation: 8481

Dim Files As Collection
Set Files = GetFilesIn("C:\")            'gets all the files
Set Files = GetFilesIn("C:\", "*.txt")   'gets all the txt files
Set Files = GetFilesIn("C:\", , "*.txt") 'gets all but the txt files

Function GetFilesIn(Folder As String, Optional Matching As String, Optional Unmatching As String) As Collection
  Dim FName As String
  Set GetFilesIn = New Collection
  If Matching = "" Then
    FName = Dir(Folder)
  Else
    FName = Dir(Folder & Matching)
  End If
  Do While FName <> ""
    If Unmatching = "" Then
      GetFilesIn.Add Folder & FName
    Else
      If Not FName Like Unmatching Then GetFilesIn.Add Folder & FName
    End If
    FName = Dir
  Loop
End Function

Upvotes: 3

Related Questions