562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
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.
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:
The code to do this is actually fairly simple, provided you can get over the mental block of a subroutine calling itself.
Here is one possible answer to the above problem - the code we're looking for is stored in a constant in this case:
'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
Dim RootFolder As Folder
Set RootFolder = fso.GetFolder("C:\")
'start the ball rolling at top level folder
NumFiles = 0
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)
'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"
'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
On Error GoTo 0
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|
25 Aytoun Street