Searching Through Many Workbooks

Written by Allen Wyatt (last updated March 27, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003


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

Using the TRUNC Worksheet Function

Want to chop off everything after a certain point in a number? The TRUNC function can help with this need.

Discover More

Word Documents from Excel Macros

You can use Excel macros to open and manipulate not just Excel workbooks, but also Word documents. This tip discusses ...

Discover More

Determining the Least Common Multiple

Need to figure out the least common multiple of a range of values? It is a snap when you use the LCM function, described ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (menu)

Changing Default Search Settings

Excel provides some great tools for finding information in a worksheet or a workbook. Changing the default settings used ...

Discover More

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

Searching for Leading Apostrophes

Take a look at the Formula bar when you select a cell that contains text, and you may see an apostrophe at the beginning ...

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 8 - 5?

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.