Private Sub CommandButton1_Click()
'define variable SOURCE to store the directory path
Dim source As String
'Assign source path to defined variable
source = Dir("D:\")
'define loop variable to collect files
Dim f As Integer
'Define condition to collect the files in a folders
'Do While Len(source) > 0
Do While source <> ""
'Loop the variable by increasing 1 for every iteration
f = f + 1
'Place the result in a first column
Cells(f, 1) = source
source = Dir
'Close the loop
Loop
'Autofit the column based on file length
Cells.EntireColumn.AutoFit
End Sub