Automated reading of multiple files contained in subfolders

Question

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 http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/f455f7d3-256e-4cc9-950d-a123da9613d2/

Answer 1

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()
            Loop
        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
        Else
            Application.Run ProcToCall, aFile
            End If
        aFile = Dir()
        Loop
    Exit Sub
ErrXIT:
    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