How do i scan multiple subfolders within an overall masterfolder?

Question

I'm new to excel macros. I would like to create a macro that reads a single masterfolder, with multiple subfolders. It is looking for .xls file in the first subfolder of every subfolder(it will keep going until it finds an .xls). Upon which it will open the file, perform an edit to the file, save and close, go back up to the previous subfolder move down to the second subfolder. Repeating until no more subfolders are within that folder. It keeps iterating through the subfolders until it has gone through all the subfolders and files with the masterfolder.

It could be 4 or 5 subfolders deep before it finds the .xls file it needs to edit.

Welcome to S.O! Have you tried anything? If so, please, provide the code, take a look to the [tour](http://stackoverflow.com/tour) and [how to ask](http://stackoverflow.com/help/how-to-ask). Friendly reminder: StackOverflow is not a "we code for you" service provider. [Introduction to VBA](https://blog.udemy.com/excel-macros-tutorial/) Hint: Try to search in the forum as well. — Sgdva, Aug 23 '16 at 13:11

vatbub · Accepted Answer · 2016-08-23T21:28:04.143

Lucky you that I have some spare time at work :)

You will need recursion for your needs. A rough pseudo code for explanation:

processFiles(folder)
    for each subfolder in folder
        for each file in subfolder
            Do modifications
        next
        call processFiles(subFolder)
    next
end

In VBA, it looks like this:

Sub openAllXlsFilesInSubDirectoriesAndModifyThem()
    Dim myPath As String
    myPath = ThisWorkbook.Path

    openAllXlsFilesInSubDirectoriesAndModifyThemRecursive (myPath)
End Sub

Private Sub openAllXlsFilesInSubDirectoriesAndModifyThemRecursive(currentFolder As String)
    ' Get a list of subdirs
    Dim fileSystem As Object
    Set fileSystem = CreateObject("Scripting.FileSystemObject")

    Dim folder
    Set folder = fileSystem.GetFolder(currentFolder)

    Dim file
    Dim Workbook

    ' Go down the folder tree
    Dim subFolder
    For Each subFolder In folder.SubFolders
        ' Go through all files in that subfolder
        For Each file In subFolder.Files
            ' Check if the file has the right extension
            Debug.Print file.Name
            If Right(file.Name, Len(file.Name) - InStrRev(file.Name, ".")) = "xls" Then
                ' Open the file
                Set Workbook = Workbooks.Open(file.Path & "\" & file.Name)

                ' Operate on the file
                Workbook.Sheets(1).Range("A1").Value = "edited"

                ' Save the file
                Workbook.Save

                ' Close the file
                Workbook.Close
            End If
        Next

        ' Check all subfolders of this subfolder
        openAllXlsFilesInSubDirectoriesAndModifyThemRecursive subFolder.Path
    Next
End Sub

How do i scan multiple subfolders within an overall masterfolder?

1 Answers1