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!