This page has 0 threads | Add post

Finding our blogs useful? Please consider helping us to share more!

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. 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.

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.

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

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.

Parts of this blog
Working with Files and Folders Worked Example 1 - Finding Text within Workbooks (this blog) Worked Example 2 - Recursively Listing Files

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

Google Sheets

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR