listing folder names and sub files

[Yuriy B]

Hi,

Is it possible for excel to list folder names and the contents of all subfolders in excel?

Just like the RUN command that generates the text file list: dir /s >listmyfolder.txt

Thanks

 

[Scott T]

You can use power query to do that. Power query is a free download for older versions of excel and included with 2016.

https://trumpexcel.com/list-file-names-power-query/

 

[Joe4]

There is no native way to do this. It will require either VBA or an add-in.
Here are some options: https://www.extendoffice.com/documents/excel/2994-excel-list-all-files-in-folder-and-subfolders.html

 

[VBA Geek]

you can do it like:

Dim FileList As String: FileList = CreateObject("Wscript.Shell").Exec("cmd /c dir ""c:\*"" /S /B").StdOut.ReadAll

to get a list of all files in a ll subfolders

you can removed the: /B to return also file sizes and a summary

 

[ranman256]

run: ScanSubfolders, after you set the foldername: HostFolder = "C:\myFoldername"

'---------
Public Sub ScanSubfolders()
'---------
Dim FileSystem As Object
Dim HostFolder As String


Range("A1").Value = "Folder"
Range("b1").Value = "File"
Range("c1").Value = "Modify Date"


Range("A2").Select
HostFolder = "C:\myFoldername\"




Set FileSystem = CreateObject("Scripting.FileSystemObject")
DoFolder FileSystem.GetFolder(HostFolder)
End Sub


'---------
Private Sub DoFolder(Folder)
'---------
Dim SubFolder
 Dim oFile
    
    For Each SubFolder In Folder.SubFolders
           'Debug.Print SubFolder
        DoFolder SubFolder
    Next
    
    For Each oFile In Folder.Files
            ' Operate on each file
       ' Debug.Print Folder, oFile.Name, getFileProperty(oFile, 5)
       If InStr(oFile, ".xls") > 0 Then
            ActiveCell.Value = Folder
            ActiveCell.Offset(0, 1).Value = oFile.Name
            ActiveCell.Offset(0, 2).Value = oFile.DateLastModified
            
            ActiveCell.Offset(1, 0).Select   'next row
       End If
    Next
End Sub

 

[rjtaylor]

<code\
Sub FindFiles()
Dim directory As String, fileName As String, i As Integer
Dim oFS As Object
Set oFS = CreateObject("Scripting.FileSystemObject")

directory = ThisWorkbook.Path & "\Word Docs\appendices\PDF Docs"
fileName = Dir(directory & "*.*")

Do While fileName <> ""
i = i + 1
MsgBox fileName
MsgBox oFS.GetFile(directory & fileName).DateLastModified
fileName = Dir()
Loop

fileName = ""
i = 0

End Sub
<code\

 

[Yuriy B]

The Power Query works great, I just wish it was a bit more customizable. I was able to delete all the extra stuff that I don't need, but I want it to separate the combined Foder path into individual columns.
Drive Path; Sub Folder 1; Sub Folder 2; Sub Folder 3; File Name.

 

[Yuriy B]

run: ScanSubfolders, after you set the foldername: HostFolder = "C:\myFoldername"

'---------
Public Sub ScanSubfolders()
'---------
Dim FileSystem As Object
Dim HostFolder As String


Range("A1").Value = "Folder"
Range("b1").Value = "File"
Range("c1").Value = "Modify Date"


Range("A2").Select
HostFolder = "C:\myFoldername\"




Set FileSystem = CreateObject("Scripting.FileSystemObject")
DoFolder FileSystem.GetFolder(HostFolder)
End Sub


'---------
Private Sub DoFolder(Folder)
'---------
Dim SubFolder
 Dim oFile
    
    For Each SubFolder In Folder.SubFolders
           'Debug.Print SubFolder
        DoFolder SubFolder
    Next
    
    For Each oFile In Folder.Files
            ' Operate on each file
       ' Debug.Print Folder, oFile.Name, getFileProperty(oFile, 5)
       If InStr(oFile, ".xls") > 0 Then
            ActiveCell.Value = Folder
            ActiveCell.Offset(0, 1).Value = oFile.Name
            ActiveCell.Offset(0, 2).Value = oFile.DateLastModified
            
            ActiveCell.Offset(1, 0).Select   'next row
       End If
    Next
End Sub

So this worked kind of. This errored out and said file path is denied or permission denied.
I also need to modify this code (would you please help with the Private Sub DoFolder(Folder)section):

'---------
Public Sub ScanSubfolders()
'---------
Dim FileSystem As Object
Dim HostFolder As String


Range("A1").Value = "Drive"
Range("b1").Value = "Subfolder 1"
Range("c1").value = "Subfolder 2"
Range("d1").value = "Subfolder 3"
Range("e1").Value = "File"
Range("f1").Value = "Modify Date"


Range("A2").Select
HostFolder = "M:"




Set FileSystem = CreateObject("Scripting.FileSystemObject")
DoFolder FileSystem.GetFolder(HostFolder)
End Sub


'---------
Private Sub DoFolder(Folder)
'---------
Dim SubFolder
 Dim oFile
    
    For Each SubFolder In Folder.SubFolders
           'Debug.Print SubFolder
        DoFolder SubFolder
    Next
    
    For Each oFile In Folder.Files
            ' Operate on each file
       ' Debug.Print Folder, oFile.Name, getFileProperty(oFile, 5)
       If InStr(oFile, ".xls") > 0 Then
            ActiveCell.Value = Folder
            ActiveCell.Offset(0, 1).Value = oFile.Name
            ActiveCell.Offset(0, 2).Value = oFile.DateLastModified
            
            ActiveCell.Offset(1, 0).Select   'next row
       End If
    Next
End Sub

 

[ranman256]

add:

'---------
Private Sub DoFolder(Folder)
'---------
Dim SubFolder
Dim oFile
    
on error resume next

 

[Yuriy B]

add:

'---------
Private Sub DoFolder(Folder)
'---------
Dim SubFolder
Dim oFile
    
on error resume next

Thank you,
Last thing to modify (please help with ?????? below):

'---------
Private Sub DoFolder(Folder)
'---------
Dim SubFolder
 Dim oFile
    On Error Resume Next
    
    For Each SubFolder In Folder.SubFolders
           'Debug.Print SubFolder
        DoFolder SubFolder
    Next
    
    For Each oFile In Folder.Files
            ' Operate on each file
       ' Debug.Print Folder, oFile.Name, getFileProperty(oFile, 5)
       If InStr(oFile, ".xls") > 0 Then
            ActiveCell.Value = ???????                          'DISPLAY DRIVE LETTER ONLY
            ActiveCell.Offset(0, 1).Value = ????????             'DISPLAY NAME OF SUBFOLDER 1
            ActiveCell.Offset(0, 2).Value = ????????                 'DISPLAY NAME OF SUBFOLDER 2
            ActiveCell.Offset(0, 3).Value = ????????                       'DISPLAY NAME OF SUBFOLDER 3
            ActiveCell.Offset(0, 4).Value = oFile.Name
            ActiveCell.Offset(0, 5).Value = oFile.DateLastModified
            ActiveCell.Offset(1, 0).Select   'next row
       End If
    Next
End Sub