This page has 0 threads | Add post

Blogs

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

Copy, rename, delete and move files and folders Part three of a three-part series of blogs
By referencing the unintuitively named Microsoft Scripting Runtime object library you can write VBA code to access files and folders on your hard disk. This blog explains how, and gives a couple of worked examples. Working with Files and Folders Worked Example 1 - Finding Text within Workbooks Worked Example 2 - Recursively Listing Files (this blog) This blog is part of a complete Excel VBA tutorial. We can also provide training in person, either on one of our Excel courses or on one of our VBA courses.

Copy, rename, delete and move files and folders

Part three of a three-part series of blogs

By referencing the unintuitively named Microsoft Scripting Runtime object library you can write VBA code to access files and folders on your hard disk. This blog explains how, and gives a couple of worked examples.

Working with Files and Folders
Worked Example 1 - Finding Text within Workbooks
Worked Example 2 - Recursively Listing Files (this blog)

This blog is part of a complete Excel VBA tutorial. We can also provide training in person, either on one of our Excel courses or on one of our VBA courses.

Posted by Andy Brown on 23 January 2012

Worked Example 2 - Recursively Listing Files

Just occasionally when programming it is almost impossible to continue without writing a subroutine which calls itself. This deeply disturbing concept works perfectly, but can be hard to understand.

Our Problem

Suppose that you want to list out all of the files on your computer which contain a given string of text (we'll use the word Owl). To do this, you need to:

Get a reference to the root folder of your hard disk.
Look at all of the files in this folder.
Look at all of the subfolders in this folder, and loop over each.
For each subfolder, repeat steps 2 and 3 above!

The code to do this is actually fairly simple, provided you can get over the mental block of a subroutine calling itself.

A Solution

Here is one possible answer to the above problem - the code we're looking for is stored in a constant in this case:

Option Explicit

'the string we're looking for in file names

Const SearchString As String = "owl"

'make the inevitable (and necessary) file system

'object public, so all routines can refer to it

Dim fso As New FileSystemObject

'count of number of files processed

Dim NumFiles As Integer

Sub StartSearch()

Dim RootFolder As Folder

Set RootFolder = fso.GetFolder("C:\")

'start the ball rolling at top level folder

NumFiles = 0

SearchFiles RootFolder

End Sub

Sub SearchFiles(fol As Folder)

'refernces to each file and subfolder respectively

Dim fil As File, subfol As Folder

'first look in all of the files in the folder

For Each fil In fol.Files

NumFiles = NumFiles + 1

If InStr(1, LCase(fil.Name), LCase(SearchString)) > 0 Then

Debug.Print "Found in " & UCase(fil.Name)

End If

'for every N files, ask user if want to continue (otherwise

'can go on for ever and be hard to interrupt)

If NumFiles >= 10000 Then

If MsgBox("Do you want to continue?", _

vbYesNo + vbDefaultButton1 + vbQuestion) = vbNo Then

MsgBox "Program aborted"

End

End If

Next fil

'now loop over all of subfolders in folder ...

For Each subfol In fol.SubFolders

'and process these too (some folders will be system ones

'which we won't have a right to open, so miss these out)

On Error Resume Next

SearchFiles subfol

On Error GoTo 0

Next subfol

End Sub

Output from the Program

On my machine, I ran the program above and aborted it at the first attempt to get:

Most people will have fewer files containing the search string OWL!

Debugging recursive macros can be very confusing. If you were to step through your code above, it would be hard to work out which instance of the SearchFiles procedure you were currently in at any moment! The call stack can be very useful in this case.

Parts of this blog
Working with Files and Folders Worked Example 1 - Finding Text within Workbooks Worked Example 2 - Recursively Listing Files (this blog)

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