make a list of all xlsx files & their sheets in a folder & its subfolder

[aimasira]

Hello,

I am new here and not familiar with VBA so i would like some help if possible.

I would like to make a list in excel to include all "xlsx" files and its sheets that will find in a specific folder and its subfolders.

Until now, i came across to 2 possible solution but both are not exactly what i am asking for.
1st solution i found it in this forum https://www.mrexcel.com/forum/excel...h-every-file-folder-list-all-sheet-names.html by ,BiocideJ, where it works perfectly however i couldn't managed to adjust the code to include subfolders or add hyperlinks due to my lack of knowledge in VBA
2nd solution i found searching with an addon application for excel "KU tools" where i could make a list of all excel files in all subfolders too with hyperlinks, however there is no option to include sheets as well

Can anyone help me?

Thanks

 

[Yongle]

Here is something modified from code found on this link
The VBA creates a list of all worksheets in all workbooks in myFolder and all its subfolders

It may take a long time to run - every workbook is opened and closed (without saving)
A delay is included to allow time for each workbook to open
You may be required to modify the code to handle password-protected workbooks etc

Insert this code in a module in a new workbook, amend myFolder string and run ListSheets

Option Explicit
Dim sList As Worksheet

Sub ListSheets()
    Const [COLOR=#008080]myFolder[/COLOR] = "[COLOR=#ff0000]C:\Test\InvoiceFolder[/COLOR]"
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set sList = Worksheets.Add(before:=ThisWorkbook.Sheets(1))
    sList.Range("A1").ColumnWidth = 50
    sList.Range("B1").ColumnWidth = 30
    ListFolders myFolder, True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub
 
Private Sub ListFolders(SourceFolderName As String, IncludeSubfolders As Boolean)
   
    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
   
    Dim r As Long, c As Long
    Dim strfile As String
    Dim wb As Workbook, ws As Worksheet, fPath As String
     
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    
    On Error Resume Next
     
    fPath = SourceFolder.Path
    strfile = Dir(fPath & "\*.xl*")
    
    If strfile <> vbNullString Then
        ActiveCell.Offset(0, 1).Select
        Do While strfile <> vbNullString
            Set wb = Workbooks.Open(fPath & "\" & strfile)
    
                Application.Wait (Now + TimeValue("00:00:01"))
                r = sList.Cells(Rows.Count, 1).End(xlUp).Row + 1
                sList.Cells(r, 1).Resize(, 2) = Array(fPath, wb.Name)
                c = 2
                For Each ws In wb.Worksheets
                   c = c + 1
                    sList.Cells(r, c) = ws.Name
                Next
            wb.Close False
            strfile = Dir
        Loop
    End If

    If IncludeSubfolders Then

        For Each SubFolder In SourceFolder.SubFolders
            ListFolders SubFolder.Path, True
        Next SubFolder
        Set SubFolder = Nothing
    End If
     
    Set SourceFolder = Nothing
    Set FSO = Nothing

End Sub

 

[Yongle]

Just spotted that you asked for .xlsx only

[I]Replace
[/I]    strfile = Dir(fPath & "\*.xl*")


[I]With   [/I]
    strfile = Dir(fPath & "\*.xl[COLOR=#ff0000]sx[/COLOR]")

 

[aimasira]

i tried to run the code, but returns an error
in the code i only changed the my folder path and the "xlsx"

the error it says "compile error, user-defined type not defined"
and upon pressing ok it highlights the following

Private Sub ListFolders(SourceFolderName As String, IncludeSubfolders As Boolean)
   
    Dim FSO As Scripting.FileSystemObject

btw i am using excel 2010

 

[Yongle]

Add a reference to Microsoft Scripting Runtime

in VBA \ Tools \ References \ scroll down to Microsoft Scripting Runtime \ check the box \ click OK