BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
In certain cases in VBA (displaying the contents of folders, listing hierarchical data) writing a program which calls itself is by far the easiest way to go - this blog gives worked examples of 3 such recursive programs.
- Recursive Programming
- Using Recursion to Display the Contents of a Folder (this blog)
- Using Recursion to Show Hierarchical Breadcrumbs
- Using Recursion to Solve Factorials
This blog is part of our Excel VBA tutorial series of blogs (consider also booking on one of our Visual Basic for Applications courses).
Posted by Andy Brown on 20 February 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Using Recursion to Display a Folder's Contents
This program uses FileSystemObjects without further explanation - you may want to refer to my blog on working with files and folders in VBA.
What this Program is Meant to do
The aim of this exercise is to list out the name and path of every single file in a folder or in any of its subfolders. Here's what we'll be aiming to list:
We'll aim to print out the names of all files in the Wise Owl folder, and in all of its subfolders (however many levels nested these may be).
Part of the output our program could produce in the Immediate window might look like this:
The recursive program will list out all files found in all folders.
The Subroutine to List Out a Folder's Files
At its heart, our program will contain a subroutine which lists out all of the files in a given folder, and then goes on to loop over all of that folder's subfolders:
Sub ListFiles(fol As Folder)
'each file within the folder
Dim fil As File
'each subfolder within the folder
Dim subfol As Folder
'hidden files may cause problems
On Error Resume Next
'list out all the files in this folder ...
If fol.Files.Count > 0 Then
Debug.Print ""
Debug.Print "FILES IN " & UCase(fol.Path); ""
Debug.Print ""
End If
For Each fil In fol.Files
Debug.Print fil.Path
Next fil
'now list out all of the files in the subfolders
For Each subfol In fol.SubFolders
ListFiles subfol
Next subfol
'reset error trapping to default
On Error GoTo 0
End Sub
I've shown the most important line in bold: the one which calls the subroutine ListFiles again, using a subfolder of the main folder as its argument.
The Routine to Start the Process
Finally, we need the code which will start the process off, by calling the ListFiles routine shown above with a folder as an argument:
Option Explicit
'need to reference Microsoft Scripting Runtime library
Dim fso As New FileSystemObject
Sub StartProcess()
'the folder whose contents we'll list
Dim fol As Folder
Set fol = fso.GetFolder("C:\Wise Owl\")
'start listing files and subfolder
ListFiles fol
End Sub
If you run the StartProcess routine above, it should list out all of the files in the Wise Owl folder and all of its subfolders!
Note that stepping through programs like this can be a nightmare, since it is hard to know which instance of the ListFiles routine you are currently debugging.
- Recursive Programming
- Using Recursion to Display the Contents of a Folder (this blog)
- Using Recursion to Show Hierarchical Breadcrumbs
- Using Recursion to Solve Factorials