CODEWITHSUNDEEP
vbaexcel
brianabrownesq
brianabrownesq

Reputation: 3

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

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

Upvotes: 0

Views: 422

Answers (1)

Vityata
Vityata

Reputation: 43595

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.

Upvotes: 1

Related Questions