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.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5598) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
The Find and Replace capabilities of Excel can come in handy, but they can't accomplish all your replacement needs. One ...
Discover MoreThe find and replace used in Excel is less powerful than its counterpart in Word, so it is not able to do some of the ...
Discover MoreWhen you are working on a worksheet (particularly a large one), you may want to search for and possibly copy information ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-02-27 04:53:16
Hello German,
I tried these modification, and it seems to work
_ 1 ) Change the variable declaration for strSearch to Variant ( This is to allow it to be used as a steering element in a For Next Loop )
_ 2 ) remove this
strSearch = "Specific text"
_3 ) Finally , this code section from Allen Wyatt needs to be wrapped inside a For Next Loop , as shown
‘_-------------------
For Each strSearch In Array("Text1", "text2") '_-------
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 strSearch '_----------------------------------------------------
‘_-_.________________________-
Alan Elston
2020-02-26 13:58:20
German
How do you get this to search for multiple things? I have a list of data or multiple items that I want to search the workbooks for. How do I change the macro to search for one thing, then move onto the next and still output the results into one sheet?
2019-12-20 11:39:56
Bruce Richards
This macro is great! I can search multiple spreadsheets in less time. Thanks
I have multiple spreadsheets, with the same sheet name in all of them, I would like to know how to modify this code so that it only searches that particular worksheet and ignores the others.
Thanks
2019-12-06 04:21:27
Matt
Absolute life saver of a VBA code!
I work in a Master Data function and was struggling to find the root cause of certain data changes that hadn't been documented properly. Quick search through all of our mass change files using this code has pinpointed exactly when changes occurred and found the problem file...saved me hours of searching! Thanks a lot!
Note - had to segregate files I was searching into smaller folders otherwise excel would hang.
2019-02-15 04:32:21
Hello jesse,
It is usually regarded as a limitation of using the “Dir way” that you cannot easily include looping through sub Folders.
Usually some sort of “Recursion Process way ” is favoured.
They can be a bit tricky to understand initially
See here for example:
h t t ps : / / w w w . excelforum.com/tips-and-tutorials/1213798-all-sub-folder-and-file-list-from-vba-recursion-routine-explanation-and-method-comparison.html
Alan Elston
2019-02-14 16:04:25
jesse branum
Can you modify this to include subfolders?
2018-10-19 00:14:18
Johan
Hello, Fantastic code. It really helps me a lot !!.
Brings me directly to another challenge. I hope to get the solution from somebody :)
How and what to change in this code to search/report on filled in colors (like vbRed) instead of a registered text.
I want to know which datafields are marked red in a few files.
regards, Johan (from the Netherlands).
2018-10-01 12:09:33
DannyCosta
Hi,
Thanks for this really very useful piece of code -works like a charm.
Anyone could make a conversion to C# or to point to a similar procedure witten in C#?
Thanks in advance for any help,
Danny
2018-04-22 13:54:24
Willy Vanhaelen
@Trevor
To copy a whole row
- select the row to copy by clicking on the row number
- press Ctrl+C : that's the copy command
- click on the row number where you want the copy to appear
- click Ctrl+V : that's the paste command
- you are done
2018-04-16 14:13:49
Trevor
I would really love to know how to get it to copy the whole row. I've searched far and wide but cannot seem to get a solution on how to just copy the whole row. If anyone could help, I would be eternally grateful.
2018-03-11 09:33:42
Skye
How to search value on specific column only (all column C) instead of searching the whole sheets cell..
2018-01-10 12:28:00
Dave Babler
How would I go about modifying this to do a find and replace across these files?
2017-12-08 04:11:43
Sam
The macro above is great, works really well. However, is there a way to search for specific text (as the macro above), then search directly below (in the cell below) it for another specific text? The way all our sheets were set up, two pieces of information were aligned one cell under the other.
2017-11-30 21:30:32
Jay
Hi, I am very new to this Excel Macro and VBA.
I have tried to insert the code in my excel and changing both the strPath and strSearch to reflect the text I would like to search.
But my excel is hang when I run this code. Anyone can advise why will it be like this and any way to resolve this?
Appreciate much if anyone can advise me on this.
Thanks.
2017-11-30 20:45:55
Jay
Hi, I am very new to this Excel Macro and VBA.
I have tried to insert the code in my excel and changing both the strPath and strSearch to reflect the text I would like to search.
But my excel is hang when I run this code. Anyone can advise why will it be like this and any way to resolve this?
Appreciate much if anyone can advise me on this.
Thanks.
2017-08-25 16:42:50
JavaPerson
Very Nice code..
Gary Lee - Could you please help me with code to search the workbooks in the folder for a 'list of strings'. I have 7-8 strings to be searched from each workbook. Thanks in advance.
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
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
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
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2020 Sharon Parq Associates, Inc.
Comments