VBA Loop Through Files in Folder

There are a few ways you can loop through files in a folder. You can do it only for a chosen folder without entering the folders inside it, or you can do it recursively and then save them to a text file.

I created a few examples, where you can see different examples and choose the one you need.

This is the treeview of directories and files.

Loop through files in a folder

This example is the most simple one. It will list all files and folders that are located in the chosen directory.

Sub LoopThroughFiles()
    Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer
    Dim fileName As Variant
    fileName = Dir("D:\mydir\")
    
    myFile = "D:\temp\list.txt"
    Open myFile For Output As #1
    
    While fileName <> ""
        If Left(fileName, 1) <> "." Then
            Write #1, fileName
        End If
        fileName = Dir
    Wend
    
    Close #1
End Sub

This code will return the list of files in D:\mydir\.

“pic1.jpg”

“pic2.jpg”

“pic3.jpg”

“text.txt”

Loop through a folder and display files and directories

If you want to list also directories, you have to add an additional parameter to the Dir function, namely vbDirectory.

Change the following line fileName = Dir(“D:\mydir\”) to fileName = Dir(“D:\mydir\”, vbDirectory)

Now, the code will return the following list.

“data”

“pic1.jpg”

“pic2.jpg”

“pic3.jpg”

“text”

“text.txt”

Loop through files recursively

Sub LoopAllSubFolders(ByVal folderPath As String)
    Dim fileName As String
    Dim fullFilePath As String
    Dim numFolders As Long
    Dim folders() As String
    Dim i As Long
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    fileName = Dir(folderPath & "*.*", vbDirectory)
    
    While Len(fileName) <> 0
    
        If Left(fileName, 1) <> "." Then
             fullFilePath = folderPath & fileName
     
            If (GetAttr(fullFilePath) And vbDirectory) = vbDirectory Then
                ReDim Preserve folders(0 To numFolders) As String
                folders(numFolders) = fullFilePath
                numFolders = numFolders + 1
                'print directories
                Write #1, fileName
            Else
                'print files
                Write #1, fileName
            End If
        End If
     
        fileName = Dir()
    Wend
    
    For i = 0 To numFolders - 1
        LoopAllSubFolders folders(i)
    Next i
End Sub

Sub loopAllSubFolderSelectStartDirector()
    Dim myFile As String
    myFile = "D:\temp\list2.txt"
    Open myFile For Output As #1
        Call LoopAllSubFolders("D:\mydir\")
    Close #1
End Sub