Searching Through Many Workbooks

by Allen Wyatt
(last updated September 21, 2013)

22

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

Error Generated if Files Opened in Word Session

Greg has a problem with Word 2002 crashing, depending on how he opens files. This seems to be a problem that Microsoft knows ...

Discover More

Viewing Your Document Full-Screen

Want to see the absolute most of your document that you can? Then you need to become familiar with the full-screen display ...

Discover More

ExcelTips: The Macros (Special Offer)

ExcelTips: The Macros is the definitive reference on how to use macros in Excel. You can stay at the top of your Excel ...

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)

Replacing Characters at the End of a Cell

The Find and Replace capabilities of Excel can come in handy, but they can't accomplish all your replacement needs. One such ...

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

Checking for Messages in Cells

If you have a range of cells used to display error messages, you soon discover that it is easy to miss messages that may ...

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}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. 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 6 - 3?

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

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.