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.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Do you often want to search through a worksheet by column rather than by row? Excel defaults to searching by row, of ...Discover More
Excel provides some great tools for finding information in a worksheet or a workbook. Changing the default settings used ...Discover More
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
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.