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.

  1. Working with Files and Folders
  2. Worked Example 1 - Finding Text within Workbooks (this blog)
  3. 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:

  1. Loop over alll of the files in the folder.
  2. For each file, check if it's an Excel workbook (see if the file extension is .XLSX or .XLSM).
  3. If it is, open the file and try to find the hidden word using the Find method.
  4. 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:

Two Excel files

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:

Result of macro

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:

Message if text not found

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. 

 

  1. Working with Files and Folders
  2. Worked Example 1 - Finding Text within Workbooks (this blog)
  3. Worked Example 2 - Recursively Listing Files
This blog has 0 threads Add post