Searching Through Many Workbooks

by Allen Wyatt
(last updated April 17, 2018)


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, _

            For Each wks In wbk.Worksheets
                Set rFound = wks.UsedRange.Find(strSearch)
                If Not rFound Is Nothing Then
                    strFirstAddress = rFound.Address
                End If
                    If rFound Is Nothing Then
                        Exit Do
                        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

            wbk.Close (False)
            strFile = Dir
    End With
    MsgBox "Done"

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

    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.


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


Understanding Ascending and Descending Sorts

When you sort information, Excel follows a set pattern of how your data is organized. This tip illuminates the burning ...

Discover More

Using the COLUMN Function

Need to know the column number for use in a formula? The worksheet function you want is the COLUMN function, described in ...

Discover More

Document Shows as 'In Use' by Another User

Word tries to constantly track who is using various documents, in order to prevent two users from clashing in their edits ...

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)

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

Searching for All

When you are working on a worksheet (particularly a large one), you may want to search for and possibly copy information ...

Discover More

Finding Text in Text Boxes

Want to search for text that may appear in a text box rather than in a regular worksheet cell? You can only perform the ...

Discover More

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

View most recent newsletter.


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}] 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 three minus 0?

2019-12-06 04:21:27


Absolute life saver of a VBA code!

I work in a Master Data function and was struggling to find the root cause of certain data changes that hadn't been documented properly. Quick search through all of our mass change files using this code has pinpointed exactly when changes occurred and found the problem file...saved me hours of searching! Thanks a lot!

Note - had to segregate files I was searching into smaller folders otherwise excel would hang.

2019-02-15 04:32:21

Alan Elston

Hello jesse,
It is usually regarded as a limitation of using the “Dir way” that you cannot easily include looping through sub Folders.
Usually some sort of “Recursion Process way ” is favoured.
They can be a bit tricky to understand initially

See here for example:
h t t ps : / / w w w .

Alan Elston

2019-02-14 16:04:25

jesse branum

Can you modify this to include subfolders?

2018-10-19 00:14:18


Hello, Fantastic code. It really helps me a lot !!.
Brings me directly to another challenge. I hope to get the solution from somebody :)
How and what to change in this code to search/report on filled in colors (like vbRed) instead of a registered text.
I want to know which datafields are marked red in a few files.

regards, Johan (from the Netherlands).

2018-10-01 12:09:33


Thanks for this really very useful piece of code -works like a charm.
Anyone could make a conversion to C# or to point to a similar procedure witten in C#?

Thanks in advance for any help,

2018-04-22 13:54:24

Willy Vanhaelen

To copy a whole row
- select the row to copy by clicking on the row number
- press Ctrl+C : that's the copy command
- click on the row number where you want the copy to appear
- click Ctrl+V : that's the paste command
- you are done

2018-04-16 14:13:49


I would really love to know how to get it to copy the whole row. I've searched far and wide but cannot seem to get a solution on how to just copy the whole row. If anyone could help, I would be eternally grateful.

2018-03-11 09:33:42


How to search value on specific column only (all column C) instead of searching the whole sheets cell..

2018-01-10 12:28:00

Dave Babler

How would I go about modifying this to do a find and replace across these files?

2017-12-08 04:11:43


The macro above is great, works really well. However, is there a way to search for specific text (as the macro above), then search directly below (in the cell below) it for another specific text? The way all our sheets were set up, two pieces of information were aligned one cell under the other.

2017-11-30 21:30:32


Hi, I am very new to this Excel Macro and VBA.
I have tried to insert the code in my excel and changing both the strPath and strSearch to reflect the text I would like to search.
But my excel is hang when I run this code. Anyone can advise why will it be like this and any way to resolve this?
Appreciate much if anyone can advise me on this.


2017-11-30 20:45:55


Hi, I am very new to this Excel Macro and VBA.
I have tried to insert the code in my excel and changing both the strPath and strSearch to reflect the text I would like to search.
But my excel is hang when I run this code. Anyone can advise why will it be like this and any way to resolve this?
Appreciate much if anyone can advise me on this.


2017-08-25 16:42:50


Very Nice code..

Gary Lee - Could you please help me with code to search the workbooks in the folder for a 'list of strings'. I have 7-8 strings to be searched from each workbook. Thanks in advance.

2017-06-02 11:36:49

Gary Lee`

Very nice. With just a few easy changes this macro can also be looped to search the workbooks in the folder for a list of strings. This just saved me about two days of manual work searching system documentation for 200+ different field references. Thanks!

2016-10-23 15:05:00

Ashish Panda

Can i do this using a userform where user inputs path of folder and text to search in separate text boxes and then clicks search command button to do the search and the result is then saved in a different sheet named "result"

2016-05-10 14:24:12

jeff palmer

I'm using this procedure inside VB6 (calling the proc) with a command button click event

I understand where the results are put and where the worksheet is made but I'm getting a
"method of worksheets global failed"

I did make a ref to excel in VB6

Is this code for excel 2000?

2016-05-10 09:51:30

jeff palmer

I understand that this routine will make a new worksheet but - where is that worksheet made? In which workbook or a new one

2015-10-09 18:48:31


Hey Allen. This helped me a lot. Just wondering though, is there a way to make it so that in cell D1 there is a clickable link to automatically open the workbook?

2015-09-23 20:40:45


Dear Allen,

Your post help me to complete a huge task. I don't know how to thank you for this help. I am a historian, very inexperient in VBA, but your explanation were so useful.

In order to get the context related to the information that I am interest in this search, it would be possible to edit the code to do the copy of the entire row where the cell with the correspondent values matches?

I would really appreciate if you could give me any tip on this.

Thanks again for the great work!

2015-08-21 04:46:12


Great Work.. It helped a lot. Thanx for Sharing

2015-07-23 23:36:23


hi, your macro is great, but is it possible if i want to search more than one strings at the same time? thanks.

2015-07-21 22:12:13


How to use the macro please suggest...

2015-07-02 01:54:31


great & very helpful... but I have to change my finding value in vbcode every time. Have you any way like a msg box appear to insert finding value..

2015-06-04 10:06:34


Is there a way to seach through multiple sub folders ?

2015-04-29 13:54:42


Thanks for the script. Works as advertised.

2015-02-16 18:10:53

Angel Lopez

This is very helpful. I was wondering if is there any way to add an Offset based on "Specific text" in order to find variables. This could be awsome.

2014-12-19 11:15:23


same how to search text in doc file vai vba
thank u

If anyone know this kindly let me know please

2014-12-18 15:41:12


hello. how can i do to show in a fifth column the value found in the right cell of the cell which contain the text i searched.
for example: i have data like this:
first_name last_name
john marcus
john ben
osama binladen
george bush

and when i searche for "john" to add in a fifth column their last_name
john marcus
john ben

2014-10-09 15:39:04


Great macro. Works just as expected. Thanks. :-)

2014-10-02 06:26:45


Awesome code.
Can it be changed in such a way that instead of changing the search criteria in VBA code, can it be done by typing in a particular cell of the sheet.

I know every thing is possible with VBA, can you please make a change as to fit for the above criteria.

Thank a lot in advance.

2014-09-05 17:56:29


Once the text is found, is there a way to copy and paste the row the text is found to the new worksheet?

2014-07-28 16:19:48


Excellent code! How about searching for multiple values (up to 100 different values) simultaneously instead of one value at a time?

2014-06-27 17:36:20


This works great. Thanks.

2014-06-13 07:39:32


great works
same how to search text in doc file vai vba
thank u

2014-05-20 13:43:50


works great - is there a way to search vba code

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

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.