Retrieve filenames from sub folder via macro

This code will allow you to select a folder then list the files in it

Option Explicit

Sub FileList()
    Dim fso, fdrSelected, filTemp, fdrTemp
    Dim strFolderPath As String
    Dim lngIndex As Long
    strFolderPath = GetFolder()
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fdrSelected = fso.GetFolder(strFolderPath)
    Cells(1, 1).Resize(, 2).Value = Array("Folders", "Files")
    lngIndex = 2
    For Each fdrTemp In fdrSelected.subFolders
        Cells(lngIndex, 1).Value = fdrTemp.Name
        lngIndex = lngIndex + 1
    Next fdrTemp
    lngIndex = 2
    For Each filTemp In fdrSelected.Files
        Cells(lngIndex, 2).Value = filTemp.Name
        lngIndex = lngIndex + 1
    Next filTemp
    Set fdrSelected = Nothing
    Set fso = Nothing
End Sub

Function GetFolder() As String
    Dim dlg As FileDialog
    Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
    If dlg.Show = -1 Then
        GetFolder = dlg.SelectedItems(1)
    End If
End Function

Select allOpen in new window

Hi,
Thanks! i have applied the formula but could not retrieve filenames from sub folders.
secondly, need to split the path in rows
lets say main dir =  Task1
sub folder =Folder 1
File name = image1
so the file path will be /task1/folder1/image1 ( need to break in rows as task1| folder1|image1)
after retrieving the files, need to compare both the path , if matching or mismatch

Hi CPH,

Please find attached, I did helped someone here sometime ago. Which will prompt you to select folder.
List-Folders---SubFolders_v2.xlsm

I have broke for D drive and E drive,

First click on List Folder D then click on List Folder E

You will get both the drive listing next to each other, then you can compare.
List-Folders---SubFolders_v3.xlsm

Hi Shums,
Thanks!
I notice the information is retrieved quick, along with full path & subfolder ( need to retrieve the file available in subfolder) which will complete the first task.

Thanks!
CPH

Hi Shums,
Sorry! did not see the second response. It is perfect. Probably remove date created|modified|link .
kindly add a formula to match between subfolder (column C & column J) with a button and the output in adjacent column.
Thanks!
CPH

Hi Shums,

Thanks a Ton.... to Shums and Roy Cox for sharing your expertise. The requirement is complete.

Thanks!
Cheiro

Hi Cheiro,

If solution provided serves your purpose, I would request you to please close this question.

Thanks! to Shums. I got a better solution.

Hi Shums,

i wish to add one more request connected to the same requirement.
While retrieving the data from the folders, it happens to include Thumbs.db in excel which is not visible in the physical folders.
This actually creates a wrong analysis while comparing data. The output excel should exclude the Thumbs.db in the report.
Please assist on the same

Thanks!
CPH

Hi CPH,

Try attached...
List-Folders---SubFolders_v5.xlsm