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 (this blog)
- Worked Example 2 - Recursively Listing Files
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 1 - Finding Text within Workbooks
The Problem (and Algorithm to Solve it)
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!
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
'open this workbook (using full file path so can find it)
Dim wb As Workbook
Dim FoundCell As Range
Set wb = Workbooks.Open(ThisFile.Path)
'look for the hidden text in each worksheet
Dim ws As Worksheet
For Each ws In Worksheets
'(this command obtained by recording)
ws.Select
Set FoundCell = Cells.Find(What:="Fu-fu", LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If FoundCell Is Nothing Then
'if no cell pointed to, text not found
IfSearchStringFound = False
Else
'if we've found a cell, display it
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
'whether we found the text or not, close the file
wb.Close savechanges:=False
End Function
Note that the function tidies up after itself (it closes down the workbook that it's opened).
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()
'the standard reference - see rest of blog
Dim fso As New FileSystemObject
'a reference to the folder containing the files
Dim WiseOwlFolder As Folder
'a reference to each file in the folder
Dim PossibleWorkbook As File
'the name of each file in the folder
Dim FileName As String
'we're looking in one particular folder,
'so try to refer to it
On Error GoTo NoSuchFolder
Set WiseOwlFolder = fso.GetFolder("C:\wise owl\")
'reset default error trapping
On Error GoTo 0
'if we get here, we've got a folder - loop over
'all of its files
For Each PossibleWorkbook In WiseOwlFolder.Files
'for this file, see if it's a workbook
FileName = PossibleWorkbook.Path
Select Case UCase(Right(FileName, 5))
Case ".XLSX", ".XLSM"
'it's a workbook, so open it
If IfSearchStringFound(PossibleWorkbook) Then
'if we've found what we were looking for,
'we can stop
Exit Sub
End If
Case Else
'for all other possible files, just ignore them
End Select
Next PossibleWorkbook
'no errors - can finish!
MsgBox "No text found in any of the files!"
Exit Sub
NoSuchFolder:
'report error and stop
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.
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.
- Working with Files and Folders
- Worked Example 1 - Finding Text within Workbooks (this blog)
- Worked Example 2 - Recursively Listing Files