List all worksheets in workbooks stored in various folders and subfolders- Excel, VBA

Question

I have been trying to write a macro that will list the sheet names of every workbook in my directory. These files are stored in various folders and subfolders, so I would like for it to pull out everything and I can extrapolate the information I need from the whole output. This code is something I've put together with the help of users from this site, but I still am not getting the desired result. Someone please help!

Sub machinegun()
    FileType = "*.xls*"
    OutputRow = 2
 Set fso = CreateObject("Scripting.FileSystemObject")
 Set Folder = fso.GetFolder("G:")
 Set Curr_File = (filepath & FileType)
 For Each Subfolder In Folder.Subfolders
    Do Until Curr_File = ""
        Set Fldr.wkbk = Workbooks.Open(filepath & Curr_File, False, True)
        ThisWorkbook.ActiveSheet.Range("A" & OutputRow) = Curr_File
        ThisWorkbook.ActiveSheet.Range("B" & OutputRow).ClearContents
        OutputRow = OutputRow + 1

        For Each Sht In FldrWkbk.Sheets
            ThisWorkbook.ActiveSheet.Range("B" & OutputRow) = Sht.Name
            ThisWorkbook.ActiveSheet.Range("A" & OutputRow).ClearContents
            OutputRow = OutputRow + 1
        Next Sht
        FldrWkbk.Close SaveChanges:=False
      Loop
Next
End Sub

The looks like it should do what you need it to do. Are you able to specify what it is that you're not happy with? — Gordon, Feb 15 '17 at 12:48
I guess my initial complaint would be that it does not yield any results... I had a version of the code that could do one folder at a time, but I need it to do nearly 400 and that's just not sustainable. — brianabrownesq, Feb 15 '17 at 12:52
Sorry, The way I read your question, it didn't sound that serious. I see now that you're not looping through the files and subfolders properly so hopefully Vityata's answer get that part sorted. — Gordon, Feb 15 '17 at 13:33

score 1 · Answer 1 · edited May 23 '17 at 12:34

Here is something from Loop Through All Subfolders Using VBA, that would work for you:

Option Explicit

Sub LoopThroughFiles()

    Dim FileSystem As Object
    Dim HostFolder As String

    HostFolder = "C:\Users\"

    Set FileSystem = CreateObject("Scripting.FileSystemObject")

    DoFolder FileSystem.GetFolder(HostFolder)

End Sub


Sub DoFolder(Folder)

    Dim SubFolder

    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next
    Dim File
    For Each File In Folder.Files
        Debug.Print File.name
    Next

End Sub

Just make sure that you run LoopThroughFiles() and change the HostFolder to something meaningful. Then work with File.name in the debug.print.

List all worksheets in workbooks stored in various folders and subfolders- Excel, VBA

1 Answers1