VBA get sheetnames from all excel files in a folder

[CaptnAbraham]

Good day dear Excelists...

I have a working code which gets me all the filenames from a given folder (with about 200+ files), looking like this:
-----------------------------------------------------------
Private Sub CommandButton2_Click()

Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$

InitialFoldr$ = "C:\Users\Name\Downloads\Folder\"

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
.Show
If .SelectedItems.Count <> 0 Then
xDirect$ = .SelectedItems(1) & "\"
xFname$ = Dir(xDirect$, 7)
Do While xFname$ <> ""
ActiveCell.Offset(xRow) = xFname$
xRow = xRow + 1
xFname$ = Dir
Loop
End If
End With

End Sub
-----------------------------------------------------------

The question is... Is there a way to also get the sheet names from within those files? Would that be a new code or is there a way to just attach that parameter here on top?
Normally there should be only one sheet per file but there might be a case where there is two (external files, who knows what silly things other people are up to). Each of the sheet names in these 200+ files are named differently.

Any help or pointers to a solution is much appreciated.

Thanks.

 

[John_w]

You have to open a workbook to read its sheet names.

Add this before the loop:

    Dim destCell As Range, wb As Workbook, i As Long
    Set destCell = ActiveCell

and replace this line:

ActiveCell.Offset(xRow) = xFname$

with these lines:

    destCell.Offset(xRow) = xFname$
    Set wb = Workbooks.Open(xDirect & xFname, ReadOnly:=True)
    For i = 1 To wb.Sheets.Count
        destCell.Offset(xRow, i) = wb.Sheets(i).Name
    Next
    wb.Close False

The code loops through the Sheets collection, which includes worksheets and chart sheets.

 

[CaptnAbraham]

Gets the job done as promised. Works perfectly for me.

Thank you, John_w!

 

[ijourneaux]

Found this thread and would like to extend it to search all sub folders. Wondering if someone already has a answer. Other wise I will continue looking.

 

[John_w]

Found this thread and would like to extend it to search all sub folders. Wondering if someone already has a answer. Other wise I will continue looking.

Search for code which uses FileSystemObject to loop through sub folders and the files in each folder (a recursive procedure) as the basis for modifying the code in this thread. Please start a new thread if you need more help.

 

[ijourneaux]

Yeap. Found it. Got what I needed. nded up using the recursive version. Now I am paying for the fact that all of these files are in one drive.