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.

  1. Working with Files and Folders
  2. Worked Example 1 - Finding Text within Workbooks
  3. 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

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.

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:

  1. Get a reference to the root folder of your hard disk.
  2. Look at all of the files in this folder.
  3. Look at all of the subfolders in this folder, and loop over each.
  4. 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

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:

Output from program

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.

  1. Working with Files and Folders
  2. Worked Example 1 - Finding Text within Workbooks
  3. Worked Example 2 - Recursively Listing Files (this blog)
This blog has 0 threads Add post