How To Use VBA To Loop Through The Files In A Folder

So, picture this…you have a client who just has the files in a folder illustrated by a picture, like a jpg.

You need the file names from the picture, but you can use an OCR (Object Character Resolution) to list the files on the image because a lot of the text shows up like garbage.

So the next bright idea you come up with is to list the files in the folder, by sending the user a VBA file with a button they can click on, select the folder, loop through the files in the folder, and print the files on a worksheet in the workbook.

Then the user can save the workbook and send it back to you.

How about that

Here’s the code to do such a thing:

Dim m_intRow As Integer

Sub Main()
    Dim fsoFileSystem As Object
    Dim strMainFolder As String
    
    m_intRow = 1

    ' Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then ' if OK is pressed
            strMainFolder = .SelectedItems(1)
        End If
    End With

    If strMainFolder <> "" Then ' if a file was chosen
        Set fsoFileSystem = CreateObject("Scripting.FileSystemObject")
        DoSubFolders fsoFileSystem.GetFolder(strMainFolder)
    Else
        MsgBox "You need to select a file folder first"
    End If
 
End Sub
 
Sub DoSubFolders(Folder)
    Dim objSubFolder As Object
 
    For Each objSubFolder In Folder.SubFolders
        
        Debug.Print "*****************************************"
        Debug.Print "SubFolder= " & objSubFolder.Name
        Debug.Print "*****************************************"
        

        DoSubFolders objSubFolder
    Next
    Dim objFile As Object
    For Each objFile In Folder.Files
        ' Operate on each file
        'Debug.Print "FileName= " & objFile.Name
        Range("A" & m_intRow) = objFile.Name
        m_intRow = m_intRow + 1
    Next
End Sub

Let me know if you have questions.