This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Writing recursive programs in VBA Part two of a four-part series of blogs
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).

Writing recursive programs in VBA

Part two of a four-part series of blogs

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

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.

Parts of this blog
Recursive Programming Using Recursion to Display the Contents of a Folder (this blog) Using Recursion to Show Hierarchical Breadcrumbs Using Recursion to Solve Factorials

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC