Automated reading of multiple files contained in subfolders

Stanley Korn 21 Reputation points

I have a folder containing subfolders, each of which contains a number of .csv files that contain data that I would like to read into a worksheet. Is there a way that I can write a VBA macro to read the data from all of the files in the subfolders without having to provide the macro with a list of those files and subfolders?

I had previously posted a question as to how to read multiple files contained in a folder that were not contained in subfolders. That question was successfully answered by @Ashidacchi in the thread at

0 comments No comments
{count} votes

Accepted answer
  1. Doug Robbins - MVP 716 Reputation points

    The following routine by Tushar Mehta will allow you to process all files in a folder and optionally all subfolders in the folder

    Option Explicit
    'A modularized solution to process all files in a folder, and optionally all subfolders in the folder _
        Tushar Mehta
    'There should be absolutely no reason to modify the searchForFiles subroutine.  Treat it as a blackbox _
     routine.  Do *not* tweak it for each specific search.
    'How to use the subroutine: _
     Call it with the four arguments: _
        DirToSearch: The directory you want to search.  Note that it must *not* end in a path separator _
            ( "\" on a Windows OS) _
        ProcToCall: This is the callback procedure called with the full name of each file found _
        FileTypeToFind: This is a search pattern for the files sought.  For example, to find all Excel _
            files use "*.xls".  This argument is optional and defaults to "*.*" (or all files) _
        SearchSubDir: Boolean that specifies whether or not to search nested folders.  The default is False.
    'The callback subroutine is where you process each file found.  The signature for the routine should be _
         Sub {subroutine-name}(ByVal aFilename As String)
    'Use this callback subroutine to do whatever it is you want to do with each file found.  For an example, _
     see the processOneFile subroutine below.
    Sub searchForFiles(ByVal DirToSearch As String, ByVal ProcToCall As String, _
            Optional ByVal FileTypeToFind As String = "*.*", _
            Optional ByVal SearchSubDir As Boolean = False)
        'by Tushar Mehta
        'This subroutine recursively calls itself if SearchSubDir is true and at least one sub-directory exists. _
         There should be no need to make any changes to this routine for any specific search.
        On Error GoTo ErrXIT
        If Right(DirToSearch, 1) = Application.PathSeparator Then _
            DirToSearch = Left(DirToSearch, Len(DirToSearch) - 1)
        If SearchSubDir Then
            Dim aFolder As String, SubFolders() As String
            ReDim SubFolders(0)
            aFolder = Dir(DirToSearch, vbDirectory)
            Do While aFolder <> ""
                If aFolder <> "." And aFolder <> ".." Then
                    SubFolders(UBound(SubFolders)) = aFolder
                    ReDim Preserve SubFolders(UBound(SubFolders) + 1)
                    End If
                aFolder = Dir()
            If UBound(SubFolders) <> LBound(SubFolders) Then
                Dim I As Long
                For I = LBound(SubFolders) To UBound(SubFolders) - 1
                    searchForFiles _
                        DirToSearch & Application.PathSeparator & SubFolders(I), _
                        ProcToCall, FileTypeToFind, SearchSubDir
                    Next I
                End If
            End If
        Dim aFile As String
        aFile = Dir(DirToSearch & Application.PathSeparator & FileTypeToFind)
        Do While aFile <> ""
            aFile = DirToSearch & Application.PathSeparator & aFile
            If (GetAttr(aFile) And vbDirectory) = vbDirectory Then
                searchForFiles aFile, ProcToCall, FileTypeToFind, SearchSubDir
                Application.Run ProcToCall, aFile
                End If
            aFile = Dir()
        Exit Sub
        MsgBox "Fatal error: " & Err.Description & " (Code=" & Err.Number & ")"
        Exit Sub
        End Sub
    'This is an example of how to use the above subroutine
    Sub ListAllFiles()
        searchForFiles "C:\tushar\temp\", "processOneFile", "*.*", True
        End Sub
    Sub processOneFile(ByVal aFilename As String)
        Debug.Print aFilename
        End Sub
    0 comments No comments

0 additional answers

Sort by: Most helpful