Option Explicit
Private lCalc As Long
Private lStatusbar As Long
Private lEvent As Long
Private lScreen As Long
Private xFileSystemObject As Object
Sub MainList()
Dim folder As Variant
Dim xDir As Variant
ThisWorkbook.Worksheets(1).Cells.ClearContents
Set folder = Application.FileDialog(msoFileDialogFolderPicker)
If folder.Show <> -1 Then Exit Sub
Call TurnOffFunctionality
xDir = folder.SelectedItems(1)
Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
Call ListFilesInFolder(xDir)
ActiveSheet.UsedRange.EntireColumn.AutoFit
Call TurnOnFunctionality
Set xFileSystemObject = Nothing
End Sub
Sub ListFilesInFolder(ByVal xFolderName As String)
Dim xFolder As Object
Dim xSubFolder As Object
Set xFolder = xFileSystemObject.GetFolder(xFolderName)
For Each xSubFolder In xFolder.SubFolders
With Application.ActiveSheet
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = xSubFolder.Name
End With
ListFilesInFolder xSubFolder.Path
Next xSubFolder
Set xFolder = Nothing
End Sub
Public Sub TurnOffFunctionality()
With Application
lCalc = .Calculation: .Calculation = xlCalculationManual
lStatusbar = .DisplayStatusBar: .DisplayStatusBar = False
lEvent = .EnableEvents: .EnableEvents = False
lScreen = .ScreenUpdating: .ScreenUpdating = False
End With
End Sub
Public Sub TurnOnFunctionality()
With Application
.Calculation = lCalc
.DisplayStatusBar = lStatusbar
.EnableEvents = lEvent
.ScreenUpdating = lScreen
End With
End Sub