557 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 two 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.
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:
Now that we've written our algorithm, it's time to code it!
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)
Set FoundCell = Cells.Find(What:="Fu-fu", LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
If FoundCell Is Nothing Then
'if no cell pointed to, text not found
IfSearchStringFound = False
'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
'whether we found the text or not, close the file
Note that the function tidies up after itself (it closes down the workbook that it's opened).
Our main routine includes error-checking to see if the workbook referenced actually exists:
'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
'for all other possible files, just ignore them
'no errors - can finish!
MsgBox "No text found in any of the files!"
'report error and stop
MsgBox "Can not find a folder with this name!"
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.
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.
|Parts of this blog|
25 Aytoun Street