List matching files in a directory
June 29, 2009 • JP • No Comments • Rate This Article
• Links to this article
In List matching folders in a directory, I posted a method for creating a list of matching folders in a specified directory.
Here is a method to list the files in a directory that match a specific file extension. This would be useful when you want to perform some operation on all files of a certain type in a folder (move, copy, open, delete, etc).
First I'll post the method with a small sample, and in the next post I'll use it in a real application that opens every Excel file in a folder and performs some calculation.
The following function, titled GetFilesList, returns a string array of filenames in the folder variable folder that have the file extension specified in fileType. Notice the parentheses after the type declaration in row 1 is what tells VBA we want it to return an array.
' build list of files in a folder folder
Dim fileslist() As String
Dim i As Long
Dim currentWorkbook As String
currentWorkbook = Dir(folder)
Do While Len(currentWorkbook) > 0
If UCase$(GetFileType(currentWorkbook)) = "." & UCase$(fileType) Then
ReDim Preserve fileslist(i)
fileslist(i) = currentWorkbook
i = i + 1
End If
currentWorkbook = Dir
Loop
GetFilesList = fileslist
End Function
Function GetFileType(ByVal fileName As String) As String
' get file extension
GetFileType = Mid$(fileName, InStrRev(fileName, "."), Len(fileName))
End Function
Usage:
Dim str() As String
Dim i As Long
Dim dummyString As String
Dim folder As String
Dim extension As String
folder = Environ("userprofile") & "\Desktop\My Files\"
extension = "xls"
str = GetFilesList(folder, extension)
' check if array is empty (i.e. no files matching extension exist in given folder)
On Error Resume Next
dummyString = str(0)
If Err <> 0 Then
MsgBox "No Files found with " & extension & " extension in " & vbCrLf & folder, vbExclamation
Exit Sub
End If
On Error GoTo 0
' str contains array of matching filenames from given folder,
' do whatever you want with it
For i = LBound(str) To UBound(str)
Debug.Print str(i)
Next i
MsgBox "Found " & UBound(str) + 1 & " file(s) with " & extension & _
" extension in folder:" & vbCrLf & folder, vbInformation
End Sub
If you want to limit the filetypes that a user can browse for, use an enumerated section like this:
xls
ppt
doc
mdb
End Enum
Then change the first line of GetFilesList to
Function GetFilesList(ByVal folder As String, ByVal fileType As FileToOpen) As String()
You would also need to convert the constant back to a string using Select Case or If statements, for comparison purposes, but now the function has custom Intellisense (or is it Auto List Members?) which limits selection to only Excel, PowerPoint, Word and Access documents.
A custom function called GetFileType, which I used in a previous post, returns the file extension for a given file. It includes the period "." as well, so the IF statement adds the period to the matching file extension to see if there's a match. ReDim Preserve is used because we don't know how many of a given file type will be in the folder. There can be zero, one, or two hundred, and the code doesn't have to be edited.
To check if the array is empty (i.e. there were no matching files in the folder), simply try to assign the first value to a string. An error is thrown if there is no value. After the "On Error GoTo 0" in the sample procedure, you can do anything you like with the array. In my sample, I printed the members of the array to the Immediate window, then used the UBound property to print a count of files found.
Next post we'll see a real example of this code in action.
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Dynamic icons and images for your Access application
Next Post: List matching files in a folder, part two
Hello,
When you type:
"Function GetFileType(ByVal fileName As String) As String
' get file extension
' assumes there are no periods in filename
' (other than the one between the filename and extension)"
Does it mean that if my file names have a period I can't use the function?
Actually that was a mistake. It does work if there are periods in the filename. Thanks for checking!