One surprising aspect of writing Visual Basic for Applications (VBA) in Excel - or any other Microsoft applicaiton for that matter - is that you can also rename, delete, copy and move files and folders on your hard disk. This tutorial shows you how!
Remember that in Excel the only built-in file-related collection that you can work with is Workbooks, which are the Excel workbooks already open in memory. You have no default way of working with files stored on your hard disk.
Creating a FileSystemObject
To play about with files and folders on your hard disk, you'll need to create a reference to the Microsoft Scripting Runtime object library, and then create a FileSystemObject. Because this is quite a common requirement, I've put it in a separate tutorial.
Getting a Folder and Listing its Files
Once you've got a variable referencing this weird thing called a FileSystemObject, you can use it as a prefix to do almost anything with the files and folders on your hard disk. One example is getting a folder and listing its contents.
For the example code below, imagine that we have a folder set up as follows:
The Wise Owl folder contains 3 files.
We want to list out in the Immediate window all of the files in this folder (perhaps because we then want to do something else with them):
We want to list out the name of the folder, and then the names of all of the files in it.
Code to get the above to work could look like this:
Sub ListFiles()
Dim fso As New FileSystemObject
Dim fol As Folder
Set fol = fso.GetFolder("C:\wise owl\")
Debug.Print "Folder name:", fol.Path
Debug.Print "============="
Debug.Print " "
Dim fil As File
For Each fil In fol.Files
Debug.Print fil.Path
Next fil
End Sub
Out of all the Microsoft applications which support VBA, the Microsoft Scripting Runtime object library is one of the most intuitive to use - as the above example shows.
Typical Methods and Properties for a File
The table below shows some of the standard things you'll want to do to a file:
What you want to do | How to do it |
|---|
Copy a file | Apply the Copy method, giving the destination as an argument |
Move a file | Apply the Move method, again giving the destination as an argument |
Delete a file | Apply the Delete method |
Rename a file | Change the file's Name property |
The rest of this tutorial gives a examples to give you the idea of what's possible!
Example 1 - Finding a Named File
Suppose that you know one of your workbooks in a folder contains the name of your dog (let's say he's called Fu-Fu), but you can't remember which. Here's an algorithm to find him:
Loop over alll of the files in the folder.
For each file, check if it's an Excel workbook (see if the file extension is .XLSX or .XLSM).
If it is, open the file and try to find the hidden word using the Find method.
As soon as you succeed, close the workbook down and abort the process, reporting what you've found.
Now that we've written our algorithm, it's time to code it!
Example 1 - A Function to Test if Text Exists in a Single Workbook
The first thing we'll do is to create a function which will take any Excel file on the hard disk, open it, look for the hidden text in all of its worksheets and return True or False depending on whether it finds it or not:
Function IfSearchStringFound(ThisFile As File) As Boolean
Dim wb As Workbook
Dim FoundCell As Range
Set wb = Workbooks.Open(ThisFile.Path)
Dim ws As Worksheet
For Each ws In Worksheets
ws.Select
Set FoundCell = Cells.Find(What:="Fu-fu", LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If FoundCell Is Nothing Then
IfSearchStringFound = False
Else
MsgBox "Found text at cell " & FoundCell.Address & _
" in worksheet " & UCase(ws.Name) & _
" in workbook " & UCase(ThisFile.Path)
IfSearchStringFound = True
Exit Function
End If
Next ws
wb.Close savechanges:=False
End Function
Note that the function tidies up after itself (it closes down the workbook that it's opened).
Example 1 - The Main Routine to Loop Over all Files in a Workbook
Our main routine includes error-checking to see if the workbook referenced actually exists:
Sub ListFiles()
Dim fso As New FileSystemObject
Dim WiseOwlFolder As Folder
Dim PossibleWorkbook As File
Dim FileName As String
On Error GoTo NoSuchFolder
Set WiseOwlFolder = fso.GetFolder("C:\wise owl\")
On Error GoTo 0
For Each PossibleWorkbook In WiseOwlFolder.Files
FileName = PossibleWorkbook.Path
Select Case UCase(Right(FileName, 5))
Case ".XLSX", ".XLSM"
If IfSearchStringFound(PossibleWorkbook) Then
Exit Sub
End If
Case Else
End Select
Next PossibleWorkbook
MsgBox "No text found in any of the files!"
Exit Sub
NoSuchFolder:
MsgBox "Can not find a folder with this name!"
End Sub
This routine will loop over all of the files in the specified folder, but only try to open the Excel ones:
For this example, the macro will open the files shown selected only, since they are the only ones whose file names end in .XLSX or .XLSM.
Example 1 - Results of Running the Macro
If Fu-fu is indeed hidden somewhere in a cell in one of the workbooks in the folder given, the macro will display this:
An example message displayed by our macro - here Fu-fu was well hidden!
If, on the other hand, there is no Fu-fu, you will see this:
The message yoi'll see if none of the workbooks contains the hidden text.
That's the end of this worked example - the next one shows how to find all of the files on your hard disk whose names contain a certain string of text.
Example 2 - Recursively Listing All 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.
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. 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
Const SearchString As String = "owl"
Dim fso As New FileSystemObject
Dim NumFiles As Integer
Sub StartSearch()
Dim RootFolder As Folder
Set RootFolder = fso.GetFolder("C:\")
NumFiles = 0
SearchFiles RootFolder
End Sub
Sub SearchFiles(fol As Folder)
Dim fil As File, subfol As 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
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
For Each subfol In fol.SubFolders
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.
