Searching Through Many Workbooks

by Allen Wyatt
(last updated September 21, 2013)

21

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.

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

Precision in Excel

The best way to understand why Excel provides the answers it does is to examine how it handles precision. This tip provides a ...

Discover More

Changing the Default Font

If you don't like the font that Excel uses, by default, in a workbook, you can change it. Here's how.

Discover More

Printing Field Codes

Field codes allow dynamic information to be included in documents and can be a great boon. At some point you may want to ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development 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 for ...

Discover More

Limitations On Finding Characters

When you search for information in a worksheet, you expect Excel to return results that make sense. If you don't get a search ...

Discover More

Finding and Replacing in Text Boxes

Finding and replacing information in a worksheet is easy. Finding and replacing in other objects (such as text boxes or chart ...

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 for this tip:

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. 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 two more than 9?

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

Gary

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

Anita

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

Himz

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


2015-07-23 23:36:23

KK

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

NANJ

How to use the macro please suggest...


2015-07-02 01:54:31

avanish

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

zeke

Is there a way to seach through multiple sub folders ?


2015-04-29 13:54:42

NH

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

vijay

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

bogdan

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

Struzak

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


2014-10-02 06:26:45

Aslam

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

Brain

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

anthony

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

Roxanne

This works great. Thanks.


2014-06-13 07:39:32

vijay

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


2014-05-20 13:43:50

cr

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

Links and Sharing