BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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
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:
- 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
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.
- Working with Files and Folders
- Worked Example 1 - Finding Text within Workbooks
- Worked Example 2 - Recursively Listing Files (this blog)