Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Searching Through Many Workbooks

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.

Related Tips:

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Ashish Panda    23 Oct 2016, 15:05
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"
jeff palmer    10 May 2016, 14:24
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?
jeff palmer    10 May 2016, 09:51
I understand that this routine will make a new worksheet but - where is that worksheet made? In which workbook or a new one
Gary    09 Oct 2015, 18:48
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?
Anita    23 Sep 2015, 20:40
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!
Himz    21 Aug 2015, 04:46
Great Work.. It helped a lot. Thanx for Sharing
KK    23 Jul 2015, 23:36
hi, your macro is great, but is it possible if i want to search more than one strings at the same time? thanks.
NANJ    21 Jul 2015, 22:12
How to use the macro please suggest...
avanish    02 Jul 2015, 01:54
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..
zeke    04 Jun 2015, 10:06
Is there a way to seach through multiple sub folders ?
NH    29 Apr 2015, 13:54
Thanks for the script. Works as advertised.
Angel Lopez    16 Feb 2015, 18:10
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.
vijay    19 Dec 2014, 11:15
same how to search text in doc file vai vba
thank u

If anyone know this kindly let me know please
bogdan    18 Dec 2014, 15:41
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
Struzak    09 Oct 2014, 15:39
Great macro. Works just as expected. Thanks. :-)
Aslam    02 Oct 2014, 06:26
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.
Brain    05 Sep 2014, 17:56
Once the text is found, is there a way to copy and paste the row the text is found to the new worksheet?
anthony    28 Jul 2014, 16:19
Excellent code! How about searching for multiple values (up to 100 different values) simultaneously instead of one value at a time?
Roxanne    27 Jun 2014, 17:36
This works great. Thanks.
vijay    13 Jun 2014, 07:39
great works
same how to search text in doc file vai vba
thank u
cr    20 May 2014, 13:43
works great - is there a way to search vba code
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.