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, _ 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.
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!
Excel provides some great tools for finding information in a worksheet or a workbook. Changing the default settings used ...Discover More
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
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
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.