Ever needed to extract a list of files within a folder/directory? Ever needed to iterate through the files within a given folder/ directory? Below is a simple illustration of how you can do it using the Dir() function. Many examples commonly utilize the File System Object (FSO), when in reality there is no need to use an external library to do this simple task! As they say Keep It Simple Stupid (the KISS philosophy).

You only truly need to consider FSO if you need a recursive function to list all the files within a directory, and, all of its sub-directories as well.
 

Basic Concept Of Using Dir To Get A File Listing

The basic concept, using the Dir() function is:

    Dim sFile as String
    Const sPath = "C:\Users\Daniel\Documents\"

    sFile = Dir(sPath & "*")
    Do While sFile <> vbNullString
        If sFile <> "." And sFile <> ".." Then
            Debug.Print sFile 'Return just the filename
            'Debug.Print sPath & sFile 'Return a fully qualified path and filename
        End If
        sFile = Dir     'Loop through the next file that was found
    Loop

Turning The Dir Solution Into a Reusable Function To List Directory Files

However, we need to transform the above into a reusable function that we can simply call in any other procedure we are working in so it returns the file listing and we can work with it there. This makes it much more versatile. As such, we could transform it into the following where the function returns an array of all the matching files it found.

'---------------------------------------------------------------------------------------
' Procedure : FF_ListFilesInDir
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return a list of files in a given directory
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPath     : Full path of folder to examine with trailing \
' sFilter   : specific file extension to limmit search to, leave blank to list all files
'
' Usage:
' ~~~~~~
' FF_ListFilesInDir("C:\Users\Daniel\Documents\") 'List all the files
' FF_ListFilesInDir("C:\Users\Daniel\Documents\","xls") 'Only list Excel files
' FF_ListFilesInDir("C:\Users\Daniel\Documents\","doc") 'Only list Word files
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Jul-13             Initial Release
' 2         2019-02-03              Updated copyright & function header
'                                   Changed function name to follow naming convention
'                                   Added \ check in sPath string
'                                   Changed the function to return an array of the files
'---------------------------------------------------------------------------------------
Function FF_ListFilesInDir(sPath As String, Optional sFilter As String = "*") As Variant
    Dim aFiles()              As String
    Dim sFile                 As String
    Dim i                     As Long

    On Error GoTo Error_Handler

    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    sFile = Dir(sPath & "*." & sFilter)
    Do While sFile <> vbNullString
        If sFile <> "." And sFile <> ".." Then
            ReDim Preserve aFiles(i)
            aFiles(i) = sFile 'Return just the filename
            'aFiles(i) = sPath & sFile 'Return a fully qualified path and filename
            i = i + 1
        End If
        sFile = Dir     'Loop through the next file that was found
    Loop
    FF_ListFilesInDir = aFiles

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FF_ListFilesInDir" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

For illustrative purposes, below is an example of a possible usage where I use a form’s open event to push a listing of a folder’s files (only text files in this example) into a list box.

'Lst_Files is the name of the list box to populate with the file listing
'   it has its 'Row Source Type' set to 'Value List'
Private Sub Form_Open(Cancel As Integer)
    Dim aFiles()              As String
    Dim sFile                 As Variant
    Dim i                     As Long
    Const sPath = "C:\Temp"       'Path to extract a list of files from

    On Error GoTo Error_Handler

    Me.Lst_Files.RowSource = ""
    aFiles = FF_ListFilesInDir(sPath, "txt")
    On Error GoTo Error_Handler_Exit
    For i = LBound(aFiles) To UBound(aFiles)
        Me.Lst_Files.AddItem aFiles(i)
    Next i

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Form_Open" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

There you have it a very flexible function you can use in any procedure to get a listing of the files contained within any folder.
 

Learn About VBA FSO

If you are wanting to learn more about what FSO can offer, then take a look at my detailed article: