Excel VBA list all sheets in a specific file

Question

I am trying to print a list of all the excel files under a specific folder. Along with the file names, I should also be able to print the names of all the sheets under the excel file.

Below is a sample of my code for printing the file names is as below. However, I am not able to figure out how to read the list of sheet names under the file?

    Sub ButtonRun_Excel_Compare_Report_Click()
    Sheet1.Cells.Clear
    ShowFolderList ("D:\temp")
End Sub

Sub ShowFolderList(folderspec)
    Dim fs, f, f1, fc, s, sFldr
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.SubFolders
     For Each f1 In fc
        If Right(f1, 1) <> "\" Then ShowFolderList f1 & "\" Else ShowFolderList f1
    Next
    Set fc = f.Files
    Rowcnt = 1
    For Each f1 In fc

        If f1.Name Like "*.xls" Then

            'How to loop through the Sheets under exach file?

            Sheet1.Cells(Rowcnt, 1) = f1.Name
            'Sheet1.Cells(Rowcnt, 2) = f1.Path
            Rowcnt = Rowcnt + 1
            'Debug.Print folderspec & f1.Name
        End If
    Next
End Sub

Appreciate the help. Thanks

If you need to cater for all sheet types (including chart sheets) you'll have to open the workbook and loop through the sheets. — Rory, Jun 24 '15 at 12:23
Use either [this](http://stackoverflow.com/questions/14358443/vba-list-of-sheets-hyperlinked) or [this](http://stackoverflow.com/questions/10654797/how-can-i-use-vba-to-list-all-worksheets-in-a-workbook-within-a-string) answer and wrap it in `Workbook.Open` and `.Close` commands. — nicolaus-hee, Jun 24 '15 at 12:34
If you don't want to open each excel workbook to get the sheet names you can use ADO and query the closed workbook. Example e.g. [here](http://www.mrexcel.com/forum/excel-questions/47074-get-worksheet-names-closed-workbook.html). — Daniel Dušek, Jun 24 '15 at 12:49
@dee unofrtunately this example is very old and only works with .XLS files. — iDevlop, Aug 29 '19 at 10:43

score 2 · Answer 1 · answered Jun 24 '15 at 12:46

You'll have to open the workbook and loop through the sheet objects

Application.Workbooks.Open Filename:=folderspec & f1.Name
For i = 1 to ActiveWorkbook.Sheets.Count
    strFlenme = ActiveWorkbook.FullName
    strShtNme = ActiveWorkbook.Sheets(i).Name
    Debug.print strFlenme & " - " & i & " '" & strShtNme & "'" 
Next

Excel VBA list all sheets in a specific file

1 Answers1