Search all workbooks in a folder and sub folders – VBA
Author: Oscar Cronquist Article last updated on February 23, 2023
This article demonstrates macros that allow you to search for a text string(s) in multiple worksheets and workbooks located in a folder or a subfolder.
What's on this page
1. Search all workbooks in a folder and subfolders using one search string
The macro asks for a folder to search in and a search string. It then opens all workbooks, one by one, in the given folder and then continues with subfolders looking for a cell containing the search string.
A new worksheet is populated data describing cells equal to the search string. The folder path, worksheet name, cell address, and a hyperlink is shown if the cell matches the search string.
Simply press with left mouse button on the hyperlink to automatically open the workbook and you will also be taken to the worksheet and cell with the matching search string.
The macro is built on the macro shown in this article:Search all workbooks in a folder . I am happy so many find it useful.
rusl cato asks:
hi thanks for the great macro really makes a hard job much easier, can this be made to search subfolders as well?
thanks
rusl
hi thanks for the great macro really makes a hard job much easier, can this be made to search subfolders as well?
thanks
rusl
Matt Durbin asks:
Is there a way to make the search look in subdirectories as well
Is there a way to make the search look in subdirectories as well
This is an example of a search i did in c:\temp on my harddrive.
The following VBA macro searches workbooks in a folder and subfolders with file extensions *.xls, *.xlsx and *.xlsm.
'Dimensioning public variable and declare data type
'A Public variable can be accessed from any module, Sub Procedure, Function or Class within a specific workbook.
Public WS As Worksheet
'Name macro and parameters
Sub SearchWKBooksSubFolders(Optional Folderpath As Variant, Optional Str As Variant)
'Dimension variables and declare data types
Dim myfolder As String
Dim a As Single
Dim sht As Worksheet
Dim Lrow As Single
Dim Folders() As String
Dim Folder As Variant
'Redimension array variable
ReDim Folders(0)
'IsMissing returns a Boolean value indicating if an optional Variant parameter has been sent to a procedure.
'Check if FolderPath has not been sent
If IsMissing(Folderpath) Then
'Add a worksheet
Set WS = Sheets.Add
'Ask for a folder to search
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
myfolder = .SelectedItems(1) & "\"
End With
'Ask for a search string
Str = Application.InputBox(prompt:="Search string:", Title:="Search all workbooks in a folder", Type:=2)
'Save "Search string:" to cell "A1"
WS.Range("A1") = "Search string:"
'Save variable Str to cell "B1"
WS.Range("B1") = Str
'Save "Path:" to cell "A2"
WS.Range("A2") = "Path:"
'Save variable myfolder to cell "B2"
WS.Range("B2") = myfolder
'Save "Folderpath" to cell "A3"
WS.Range("A3") = "Folderpath"
'Save "Workbook" to cell "B3"
WS.Range("B3") = "Workbook"
'Save "Worksheet" to cell "C3"
WS.Range("C3") = "Worksheet"
'Save "Cell Address" to cell "D3"
WS.Range("D3") = "Cell Address"
'Save "Link" to cell "E3"
WS.Range("E3") = "Link"
'Save variable myfolder to variable Folderpath
Folderpath = myfolder
'Dir returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.
Value = Dir(myfolder, &H1F)
'Continue here if FolderPath has been sent
Else
'Check if two last characters in Folderpath is "//"
If Right(Folderpath, 2) = "\\" Then
'Stop macro
Exit Sub
End If
'Dir returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.
Value = Dir(Folderpath, &H1F)
End If
'Keep iterating until Value is nothing
Do Until Value = ""
'Check if Value is . or ..
If Value = "." Or Value = ".." Then
'Continue here if Value is not . or ..
Else
'Check if Folderpath & Value is a folder
If GetAttr(Folderpath & Value) = 16 Then
'Add folder name to array variable Folders
Folders(UBound(Folders)) = Value
'Add another container to array variable Folders
ReDim Preserve Folders(UBound(Folders) + 1)
'Continue here if Value is not a folder
'Check if file ends with xls, xlsx, or xlsm
ElseIf Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
'Enable error handling
On Error Resume Next
'Check if workbook is password protected
Workbooks.Open Filename:=Folderpath & Value, Password:="zzzzzzzzzzzz"
'Check if an error has occurred
If Err.Number <> 0 Then
'Write the workbook name and the phrase "Password protected"
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = "Password protected"
'Add 1 to variable 1
a = a + 1
'Disable error handling
On Error GoTo 0
'Continue here if an error has not occurred
Else
'Iterate through all worksheets in active workbook
For Each sht In ActiveWorkbook.Worksheets
'Expand all groups in sheet
sht.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8
'Search for cells containing search string and save to variable c
Set c = sht.Cells.Find(Str, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
'Check if variable c is not empty
If Not c Is Nothing Then
'Save cell address to variable firstAddress
firstAddress = c.Address
'Do ... Loop While c is not nothing
Do
'Save row of last non empty cell in column A
Lrow = WS.Range("A" & Rows.Count).End(xlUp).Row
'Save folderpath to the first empty cell in column A in worksheet WS
WS.Range("A1").Offset(Lrow, 0).Value = Folderpath
'Save value to the first empty cell in column B in worksheet WS
WS.Range("B1").Offset(Lrow, 0).Value = Value
'Save worksheet name to the first empty cell in column C in worksheet WS
WS.Range("C1").Offset(Lrow, 0).Value = sht.Name
'Save cell address to the first empty cell in column D in worksheet WS
WS.Range("D1").Offset(Lrow, 0).Value = c.Address
'Insert hyperlink
WS.Hyperlinks.Add Anchor:=WS.Range("E1").Offset(Lrow, 0), Address:=Folderpath & Value, SubAddress:= _
"'" & sht.Name & "'" & "!" & c.Address, TextToDisplay:="Link"
'Find next cewll containing search string and save to variable c
Set c = sht.Cells.FindNext(c)
'Continue iterate while c is not empty and cell address is not equal to first cell address
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
'Continue with next worksheet
Next sht
End If
'Close workbook
Workbooks(Value).Close False
'Disable error handling
On Error GoTo 0
End If
End If
Value = Dir
Loop
'Go through alll folder names and
For Each Folder In Folders
'start another instance of macro SearchWKBooksSubFolders (recursive)
SearchWKBooksSubFolders (Folderpath & Folder & "\")
Next Folder
'Resize column widths
Cells.EntireColumn.AutoFit
End Sub
Microsoft docs: IsMissing | Dir | GetAttr |
Update:
- The macro expands grouped rows and columns before searching a workbook.
- There is also now a class module in the workbook below, so if you press with left mouse button on a hyperlink on a sheet and a workbook opens, grouped data will be expanded.
- Worksheet names containing some specific characters caused "Reference is not valid" if you press with left mouse button on a hyperlink. This is now working.
2. Search all workbooks in a folder and subfolders using multiple search strings
'Dimensioning public variable and declare data type
'A Public variable can be accessed from any module, Sub Procedure, Function or Class within a specific workbook.
Public WS As Worksheet
'Name macro and parameters
Sub SearchWKBooksSubFolders(Optional Folderpath As Variant, Optional Str As Variant)
'Dimension variables and declare data types
Dim myfolder As String
Dim a As Single
Dim sht As Worksheet
Dim Lrow As Single
Dim Folders() As String
Dim Folder As Variant
'Redimension array variable
ReDim Folders(0)
'IsMissing returns a Boolean value indicating if an optional Variant parameter has been sent to a procedure.
'Check if FolderPath has not been sent
If IsMissing(Folderpath) Then
'Add a worksheet
Set WS = Sheets.Add
'Ask for a folder to search
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
myfolder = .SelectedItems(1) & "\"
End With
'Ask for a search string
'Str = Application.InputBox(prompt:="Search string:", Title:="Search all workbooks in a folder", Type:=2)
'Show dialog box and ask for a cell range
Set RNG = Application.InputBox(Prompt:="Select a cell range containing search strings" _
, Title:="Select a range", Default:=ActiveCell.Address, Type:=8)
'Stop macro if no search string is entered.
'If Str = "" Then Exit Sub
'Save "Search string:" to cell "A1"
WS.Range("A1") = "Search string:"
'Save variable Str to cell "B1"
'WS.Range("B1") = Str
'Save "Path:" to cell "A2"
WS.Range("A2") = "Path:"
'Save variable myfolder to cell "B2"
WS.Range("B2") = myfolder
'Save "Folderpath" to cell "A3"
WS.Range("A3") = "Folderpath"
'Save "Workbook" to cell "B3"
WS.Range("B3") = "Workbook"
'Save "Worksheet" to cell "C3"
WS.Range("C3") = "Worksheet"
'Save "Cell Address" to cell "D3"
WS.Range("D3") = "Cell Address"
'Save "Link" to cell "E3"
WS.Range("E3") = "Link"
'Save "Search string" to cell "F3"
WS.Range("F3") = "Search string"
'Save variable myfolder to variable Folderpath
Folderpath = myfolder
'Dir returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.
Value = Dir(myfolder, &H1F)
'Continue here if FolderPath has been sent
Else
'Check if two last characters in Folderpath is "//"
If Right(Folderpath, 2) = "\\" Then
'Stop macro
Exit Sub
End If
'Dir returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.
Value = Dir(Folderpath, &H1F)
End If
'Keep iterating until Value is nothing
Do Until Value = ""
'Check if Value is . or ..
If Value = "." Or Value = ".." Then
'Continue here if Value is not . or ..
Else
'Check if Folderpath & Value is a folder
If GetAttr(Folderpath & Value) = 16 Then
'Add folder name to array variable Folders
Folders(UBound(Folders)) = Value
'Add another container to array variable Folders
ReDim Preserve Folders(UBound(Folders) + 1)
'Continue here if Value is not a folder
'Check if file ends with xls, xlsx, or xlsm
ElseIf Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
'Enable error handling
On Error Resume Next
'Check if workbook is password protected
Workbooks.Open Filename:=Folderpath & Value, Password:="zzzzzzzzzzzz"
'Check if an error has occurred
If Err.Number <> 0 Then
'Write the workbook name and the phrase "Password protected"
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = "Password protected"
'Add 1 to variable 1
a = a + 1
'Disable error handling
On Error GoTo 0
'Continue here if an error has not occurred
Else
'Iterate through all worksheets in active workbook
For Each sht In ActiveWorkbook.Worksheets
'Expand all groups in sheet
sht.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8
'Iterate through all cells in variable RNG
For Each Str In RNG
'Search for cells containing search string and save to variable c
Set c = sht.Cells.Find(Str, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
'Check if variable c is not empty
If Not c Is Nothing Then
'Save cell address to variable firstAddress
firstAddress = c.Address
'Do ... Loop While c is not nothing
Do
'Save row of last non empty cell in column A
Lrow = WS.Range("A" & Rows.Count).End(xlUp).Row
'Save folderpath to the first empty cell in column A in worksheet WS
WS.Range("A1").Offset(Lrow, 0).Value = Folderpath
'Save value to the first empty cell in column B in worksheet WS
WS.Range("B1").Offset(Lrow, 0).Value = Value
'Save worksheet name to the first empty cell in column C in worksheet WS
WS.Range("C1").Offset(Lrow, 0).Value = sht.Name
'Save cell address to the first empty cell in column D in worksheet WS
WS.Range("D1").Offset(Lrow, 0).Value = c.Address
'Insert hyperlink
WS.Hyperlinks.Add Anchor:=WS.Range("E1").Offset(Lrow, 0), Address:=Folderpath & Value, SubAddress:= _
"'" & sht.Name & "'" & "!" & c.Address, TextToDisplay:="Link"
'Save search string to the first empty cell in column F in worksheet WS
WS.Range("F1").Offset(Lrow, 0).Value = Str
'Find next cewll containing search string and save to variable c
Set c = sht.Cells.FindNext(c)
'Continue iterate while c is not empty and cell address is not equal to first cell address
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
'Continue with the next str
Next Str
'Continue with next worksheet
Next sht
End If
'Close workbook
Workbooks(Value).Close False
'Disable error handling
On Error GoTo 0
End If
End If
Value = Dir
Loop
'Go through alll folder names and
For Each Folder In Folders
'start another instance of macro SearchWKBooksSubFolders (recursive)
SearchWKBooksSubFolders (Folderpath & Folder & "\")
Next Folder
'Resize column widths
Cells.EntireColumn.AutoFit
End Sub
3. Where to put the code?
- Press shortcut keys Alt + F11 to open the Visual Basic Editor (VBE).
- Press the left mouse button on "Insert" on the top menu, see the image above.
- Press the left mouse button on "Module" to insert a module to your workbook.
- A new module named Module1 is now shown in the "Project Explorer", see the image above.
- Copy and paste the VBA code to the code module window.
- Go back to Excel.
Now save your workbook with the file extension *.xlsm in order to keep the code attached to your workbook.
3.1 How to run the macro
- Press Alt + F8 to open the macro dialog box.
- Select macro named "SearchWKBooksSubFolders".
- Press with the left mouse button on button "Run".
3.2 How to use the macro
The following steps explain how to use the SearchWKBooksSubFolders described in section 2.
- A dialog box opens asking for a folder to use.
- A dialog box shows up asking for a cell range containing the search strings.
- The macro iterates through all workbooks in the selected folder looking for the search strings.
- A worksheet is created and search results are presented. The image below shows an example.
Files and folders category
Compare file names in two different folder locations and their sub folders
Today I would like to share a macro that compares the content in two different folders and their subfolders. It […]
Today I would like to share a macro that compares the content in two different folders and their subfolders. It […]
Copy data from workbooks in folder and subfolders
I will in this article demonstrate a macro that automatically opens all workbooks in a folder and subfolders, one by […]
I will in this article demonstrate a macro that automatically opens all workbooks in a folder and subfolders, one by […]
Filter duplicate files in a folder and subfolders
This is a follow up to my last post Compare file names in two different folder locations and their subfolders, the obvious […]
This is a follow up to my last post Compare file names in two different folder locations and their subfolders, the obvious […]
Excel categories
29 Responses to “Search all workbooks in a folder and sub folders – VBA”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
[…] Search all workbooks in a folder and sub folders […]
Have you thought about adding -
Application.ScreenUpdating = False at the start and
Application.ScreenUpdating = True at the end
as it will stop all the frantic screen changes when the macro is running?
It also left the last file opened open.
There is also something not quite right if one of the files is password protected - the next couple also showed up as password protected but with a link.
See below (I have lined up the columns as they appear in the sheet)-
Search string: Flexi
Path: C:\Users\AEvans\Documents\
Folderpath Workbook Worksheet Cell Address Link
003474-Disc-wastage.xls Password protected
Access to offices.xls Password protected
Bank Account Balance.xls Password protected Sheet4 $A$540 Link
Bank Account Balance2.xlsx Password protected Sheet4 $A$715 Link
endeavour usage.xls Password protected Sheet4 $A$542 Link
C:\Users\AEvans\Documents\AEvans\ Accountancy Usage 20030818.xls Sheet4 $A$700 Link
C:\Users\AEvans\Documents\AEvans\ accountancydiscusage.xls Sheet4 $A$453 Link
Andrew Evans,
Have you thought about adding -
Application.ScreenUpdating = False at the start and
Application.ScreenUpdating = True at the end
as it will stop all the frantic screen changes when the macro is running?
No, I used it for monitoring the process but it is a great idea. It may also speed things up.
It also left the last file opened open.
There is also something not quite right if one of the files is password protected - the next couple also showed up as password protected but with a link.
I will look into that.
Thank you for commenting!
Many thanks for this. Would you be able to modify the code, so that rather than searching for a single value, it searches for all values in a specific column, in a specific worksheet, in a workbook?
Or better still, all values in a specific column, in ALL worksheets, in a workbook? With each search term's results on a separate worksheet, named fter the search term?
Oops, I meant search for that list of values in all workbooks in the folder & subfolders, not just one.
Hey,
First of all I'd like to say that this is a great code and has been very useful. But isn't this the same code as for one folder without subfolders? I couldn't find differences in the codes. And this code mentioned above doesn't work for subfolders, could you please check that?
kind regards
Stijn,
But isn't this the same code as for one folder without subfolders?
You are right, I have changed the code in this post but the file was correct.
And this code mentioned above doesn't work for subfolders, could you please check that?
If you are talking about the issue with passprotected workbooks, I believe I have solved that and there is now a updated file for you to get in this post.
Hi
Fantastic code !
I am trying to use your workbook to search my excel files.
My problem is that the data in the tabs is from a system output (think it must have been a csv file ?).
As a result. When I search using value it can't find the match. I tried changing the LookIn:=Value to Text, formula, value2 but can't get anything to find it.
Is there a way to adapt your code so it can search all contents within a tab ? Eg cell D1 has the value I am searching for contained in the text string (as opposed to a value in its own cell.
Any help / ideas would be truly appreciated.
Long shot - but, will MatchCase:= False SearchFormat:=False help me?
I can find the value when I uncheck the match check boxes (searching manually using Find).
Can't get that to work so not sure if I am going down a wrong path :-)
Phew - found it - changed LookAt:=Whole to LookAt:=xlPart
Fantastic code - many thanks :-)
Hi,
First a thanks for writing this - I'm new to Excel Macros and having working examples to play with is great for me to learn.
However, I'm having trouble with this macro - whenever I run it, it completely crashes (sorry can't provide error messages)!
I've used the "Search all workbooks in a folder", and it works perfectly. Equally, this macro works fine if there are no subfolders in the folder I select so it seems to be an issue with cycling through the subfolders?
I'm using Excel 2010. Any suggestions for what is happening would be helpful :)
Hi, i'm very new to this. I was wondering if you could do the search on the main page in Excel for example updating the search string cell would automatically run the search rather than going into VB in excel?
Stephen,
Yes it is possible. You need to create a sheet event that runs the macro whenever a specific cell value changes.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B1")) Is Nothing Then Call SearchWKBooksSubFolders(, Target.Value) End If End SubI had to do a minor change to the macro also.
Here is your version:
Search-multiple-workbooks-in-a-folder-and-sub-foldersv2.xlsm
You don't need to do anything except enter a value in cell B1 on sheet "Sheet1" and the macro is automatically executed.
Hi Oscar,
This is great, nearly what I am looking for. Is it possible to search for 2 or 3 criteria as in searching for "HOK AND DRE AND WAI"?
Thanks Rick
Hello Oscar,
Thank you for this fantastic code. I am very new to this and this has helped me massively.
So for something else I need to do, 2 questions:
1. Can this be modified so instead of 'workbooks' it searches for files that match partial filenames? The partial filenames are strings of numbers with 10 digits.
Would something like this work???
2. When listing search results, can it list out the filepaths of multiple files matching the partial filenames? e.g. 1234567890 returns 5 results, I need all 5 to be listed so I can find them.
Many thanks in advance.
Yours sincerely,
Alupha
1.use this code for multibyte enviroment.
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
"'" & sht.Name & "'!" & c.Address, TextToDisplay:="Link"
2.Error occur if c is nothing.so exit before loop.
If c Is Nothing Then
Exit Do
End If
Loop While c.Address firstAddress
Thank you very much for the very useful piece of code. I modified the code for locating workbooks with a specified text in a sheets name. The Value = Dir at the end before the Loop was picking file names starting with "~", which I couldn't locate using File explorer (show hidden files). Got over the problem by ignoring files starting with "~" using the code "And (Left(Value, 1) "~"). I also had to use Call WorkSheetsInWKBooksSubFolders(Folderpath & Folder & "\", Str), because otherwise the value of Str was not retained in second and subsequent calls.
Again than you very much for the very useful template, which works great.
Regards
Jason
Hi
Great VBA-code.
Is it possible to have the same function for the multiple folder and subfolders as the vba that search and return a value?
I seem to be running into an issue with lock files that were left behind such as
~$January 12, 2021 Ryan.xlsx.
The macro locks up when it comes across one.
What is the best way to skip over these?
I seem to be having issues with this as it's not iterating through any of the subfolders. I've kept your code the same.
Hi,
many thanks for this code. Very helpful.
I need to search 280 strings though. What does need to be changed so that I can choose all strings to be searched at once and that the results are all shown in one tab (rather than 280 separate tabs) ?
Thank you
WOW, So clean and fantastic code which is helpful.
Can I loop the code in multiple values in A column for example.
That will be great help for many values to be found at once.
Martel,
thank you.
I have created a new macro that lets you use multiple search values. See section 2 above.
Many thanks Oscar for your reply back. I tried the macro, and unfortunately it is not working with me. I noticed that the Str value was taken from the user input box. However, when you replace it with RNG for multiple search values, the Str will remain empty as no value is assigned "If Str = "" Then Exit Sub", and then the macro gives Run-time error 13 (Type mismatch". I hope that my analysis helped you to identify the issue.
Hi, Again Oscar. I tried with my limited experience to bypass the If statement and noticed that the macro gets only the last search value in the range entered with all details except the string value in the F column.
Martel,
I found the error, I changed the code. Please try it again.
This is what I get when I run the macro:
Hi Oscar, I am trying the macro in Section 2, and still getting Run-time error '13': Type mismatch pointing on the line (If Str = "" Then Exit Sub). Is there any additional library that should be included rather than the default ones?
Martel,
I commented that line out, somehow that change didn't show up on the webpage.
It should look like this:
'If Str = "" Then Exit Sub
You can also remove it entirely.
Thanks Oscar, you are really supportive, and sorry to bother you alot.
I comment the line and it is now working fine without any error.
But I noticed that it gives the results only to the last string in the giving range.
Any ideas ..