Get List of All Files inside Main & Sub Folder – Excel VBA

This is in Continuation with our Previous Post Get List of All Files inside a Folder-Excel VBA 

Here we have added Sub Main and put Sub FilesInsideFolder outside main sub .

sub clearCells  is to clear all cells before running FilesInsideFolder sub routine.

Starting Row  changed from r=2 to  r = Cells(Rows.Count, “A”).End(xlUp).Row + 1 because now when we include subfolder files along with main files then we need to list them down in next row.

We called FilesInsideFolder sub routine by using following method
Call FilesInsideFolder(sFolder, True)

Here we are passing two arguments sFolder into myFolder and True value in includeSubFolder


‘This code is for Reference only by ITechOasis.com
Dim FSO As Scripting.FileSystemObject
Dim sFolder As Scripting.Folder
Dim subFolder As Scripting.Folder
Dim sFile As Scripting.File
Dim sPath As String
Dim r As Long
Dim includeSubFolder As Boolean

Sub main()

Set FSO = New Scripting.FileSystemObject
sPath = “C:\temp” ‘Give any Path name you want
Set sFolder = FSO.GetFolder(sPath)

‘Clear Content
Call clearCells

‘List All Files inside Main and Sub Folder
Call FilesInsideFolder(sFolder, True)

‘ Coulmn Width
Columns(“A”).ColumnWidth = 10
Columns(“B”).ColumnWidth = 30
Columns(“C”).ColumnWidth = 60

End Sub

Sub FilesInsideFolder(myFolder As Scripting.Folder, includeSubFolder As Boolean)

‘Title Names
Range(“A1”).Value = “Sr. No.”
Range(“B1”).Value = “File Name”
Range(“C1”).Value = “File Path”
Range(“A1:C1”).Font.Bold = True

‘ Starting Row
r = Cells(Rows.Count, “A”).End(xlUp).Row + 1

On Error Resume Next
For Each sFile In myFolder.Files
Cells(r, 1).Formula = r – 1 ‘Serial Number starts from row 2
Cells(r, 2).Formula = sFile.Name ‘ File Name with Extension
Cells(r, 3).Formula = sFile.Path
r = r + 1
Next sFile

If includeSubFolder Then
For Each subFolder In myFolder.SubFolders
FilesInsideFolder subFolder, True
Next subFolder
End If
End Sub

Sub clearCells()
Cells.Clear
End Sub


 

Leave a Comment