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
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
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. |
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:
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
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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.