How to read the filenames of all the files in a directory into an array

For instance, in order to populate a list box

Article contributed by Dave Rado

You can use the Dir$ function to do this.  (Dir and Dir$ are functionally identical, but Dir$ runs marginally faster).  

Dir$ is a lot faster than using FileSearch, unless many subdirectories need to be searched or you need to use the advanced features of FileSearch such as the LastModified property.

Dim MyFile As String
Dim Counter As Long

'Create a dynamic array variable, and then declare its initial size
Dim DirectoryListArray() As String
ReDim DirectoryListArray(1000)

'Loop through all the files in the directory by using Dir$ function
MyFile = Dir$("c:\temp\*.*")
Do While MyFile <> ""
    DirectoryListArray(Counter) = MyFile
    MyFile = Dir$
    Counter = Counter + 1

'Reset the size of the array without losing its values by using Redim Preserve 
Redim Preserve DirectoryListArray(Counter - 1)

To prove it worked you could run the following:

For Counter = 0 To UBound(DirectoryListArray)
    'Debug.Print writes the results to the Immediate window (press Ctrl + G to view it)'
    Debug.Print DirectoryListArray(Counter)
Next Counter

To populate a Listbox from the array you could use:

ListBox1.List =  DirectoryListArray

Click to view Terms of Use page

Click to view Disclaimer page