Searching Through Many Workbooks

by Allen Wyatt
(last updated March 27, 2021)

3

Amit has a folder that contains hundreds of Excel workbooks. He needs to search through all the workbooks for some specific text and wonders if there is a way to search through all the workbooks and determine the names of the workbooks that contain the desired text, along with the cells in the workbooks that contain that text.

Finding which workbooks contain the desired text is relatively easy. All you need to do is use the Search capabilities of Windows to look for files, in the single folder, that contain the desired text. While it won't tell you the cell locations, it will winnow down the list of files.

Of course, you can use a macro to do your searching for you. (It's always a good idea to use a macro to do the long, tedious work that would otherwise be done manually.) The following will step through all the workbooks in a folder and search for what you want to locate. It will open any file ending in xls* (the trailing asterisk means that it will search for xls, xlsx, and xlsm files).

Sub SearchFolders()
    Dim fso As Object
    Dim fld As Object
    Dim strSearch As String
    Dim strPath As String
    Dim strFile As String
    Dim wOut As Worksheet
    Dim wbk As Workbook
    Dim wks As Worksheet
    Dim lRow As Long
    Dim rFound As Range
    Dim strFirstAddress As String

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    'Change as desired
    strPath = "c:\MyFolder"
    strSearch = "Specific text"

    Set wOut = Worksheets.Add
    lRow = 1
    With wOut
        .Cells(lRow, 1) = "Workbook"
        .Cells(lRow, 2) = "Worksheet"
        .Cells(lRow, 3) = "Cell"
        .Cells(lRow, 4) = "Text in Cell"
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set fld = fso.GetFolder(strPath)

        strFile = Dir(strPath & "\*.xls*")
        Do While strFile <> ""
            Set wbk = Workbooks.Open _
              (Filename:=strPath & "\" & strFile, _
              UpdateLinks:=0, _
              ReadOnly:=True, _
              AddToMRU:=False)

            For Each wks In wbk.Worksheets
                Set rFound = wks.UsedRange.Find(strSearch)
                If Not rFound Is Nothing Then
                    strFirstAddress = rFound.Address
                End If
                Do
                    If rFound Is Nothing Then
                        Exit Do
                    Else
                        lRow = lRow + 1
                        .Cells(lRow, 1) = wbk.Name
                        .Cells(lRow, 2) = wks.Name
                        .Cells(lRow, 3) = rFound.Address
                        .Cells(lRow, 4) = rFound.Value
                    End If
                    Set rFound = wks.Cells.FindNext(After:=rFound)
                Loop While strFirstAddress <> rFound.Address
            Next

            wbk.Close (False)
            strFile = Dir
        Loop
        .Columns("A:D").EntireColumn.AutoFit
    End With
    MsgBox "Done"

ExitHandler:
    Set wOut = Nothing
    Set wks = Nothing
    Set wbk = Nothing
    Set fld = Nothing
    Set fso = Nothing
    Application.ScreenUpdating = True
    Exit Sub

ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

To customize the routine for your needs, change the strPath variable to reflect the path to the folder you want to process and change strSearch to reflect the text for which you are searching. The macro creates a new worksheet and places "hits" into each row. Column A contains the workbook name, column B the worksheet name, column C the cell address, and column D the contents of that cell.

Obviously, any macro like this one takes quite a bit of time to run. You can shorten the time somewhat by reducing the number of files it needs to search. The best way to do this is to use the Windows Search approach (described at the beginning of this tip) to identify the workbooks in which the desired text resides. Move those workbooks to their own folder and then do the macro search on that folder.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5598) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Unable to Format Cells

If you ever get to a situation where you can no longer format cells in a workbook, you'll realize just how important the ...

Discover More

Counting All Graphics

Need to know how many graphics a document contains? Getting at the true number may take a little more work than it first ...

Discover More

Linking Comments to Multiple Cells

In Excel, single comments are associated with single cells. If you want to have a comment be linked to multiple cells, ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (menu)

Wildcards in 'Replace With' Text

When doing searches in Excel, you can use wildcard characters in the specification of what you are searching. However, ...

Discover More

Making All Occurrences Bold

Want to make instances of a given word or phrase bold throughout a worksheet? Here's a way you can make the change quickly.

Discover More

Using Find and Replace to Pre-Pend Characters

Need to add some characters to the beginning of the contents in a range of cells? It's not as easy as you might hope, but ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 - 3?

2021-08-26 11:27:15

Fabi

Hi Allen,
This is very helpful. Thank you for sharing your genius. You were the first hit on my Google search so you help countless numbers of people like me.
I need a "small" modification to the code. Instead of, or in addition to, copying to column D the contents of the cell from the search results, I need to copy the entire row of the located text.

I'm still searching for how to do this but if you have the answer off the top of your head, I'd sure appreciate a reply to this comment.

Have a great day!
Fabi


2021-05-24 18:41:42

peji

HI There

is there any way to search in all workbooks in folders and subolders ?
i could not find anywhere , some found but had bugs .

many thanks


2021-04-26 04:13:44

Simon

Dear Allen, Thank you very much for sharing this clean and efficient piece of code. The work of a true professional!


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.