I write this function many many times in my projects. When we have to create a temporary folder which are specific to our application, then I normally have to loop through all the files in a folder and put some validations so that my code should not replace any previous temp or log files. So I thought to share a small snippet of Excel VBA code where I am trying to search all the files in a Folder ( and its subfolders too) and printing in an Excel Worksheet.
Private fileCounter As Integer
Private activeSht As Worksheet
'Display all the files in a folder. Searches all the sub folders.
'Prints Folder Names in Column A and and the file Names in Column B
Dim pth As String
Dim fso As FileSystemObject
Dim baseFolder As Folder
pth = "C:\Projects\" 'the base path which has to be searched for Files
Set fso = New FileSystemObject
''check if the folder actually exists or not
If (Not (fso.FolderExists(pth))) Then
'the folder path is invalid. Exiting.
MsgBox "Invalid Path"
Set baseFolder = fso.GetFolder(pth)
fileCounter = 1
Set activeSht = ActiveSheet
activeSht.Range("A1").Value = "Folder Name"
activeSht.Range("B1").Value = "File Name"
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Sub PrintFileNames(baseFolder As Folder)
Dim folder_ As Folder
Dim file_ As File
For Each folder_ In baseFolder.SubFolders
'call recursive function.
For Each file_ In baseFolder.Files
'print files here
activeSht.Range("A1").Offset(fileCounter, 0).Value = baseFolder.Path
activeSht.Range("B1").Offset(fileCounter, 0).Value = file_.Name
fileCounter = fileCounter + 1
The above code loops through all the subfolders and the files in a given folder and prints the folder names in Column A and the file Names in Column B.