Search all workbooks in a folder
Author: Oscar Cronquist Article last updated on September 13, 2024
Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a specific string.
What's on this page
- Search all workbooks in a folder using one string
- Search all workbooks in a folder using multiple strings
- Search all workbooks in a folder using multiple strings and return corresponding value(s) on the same row
- Search all workbooks in a folder using other file extensions
- Where to put the code
- Search all workbooks in a folder and sub folders - VBA
You can search workbooks with other file extensions as well, I will demonstrate what to change in the macro, later in this post.
The macro creates a new worksheet containing the search result. Each result contains a link to a cell where the search string was found.
You can then press with left mouse button on the link and Excel opens the corresponding workbook and locates the cell containing the search string.
There are also macros demonstrated here that allow you to search for multiple values and return a corresponding on the same row.
The following macro asks for a folder to search in and the search string.
Search workbooks in folder for a single string
'Name macro
Sub SearchWKBooks()
'Dimension variables and declare data types
Dim WS As Worksheet
Dim myfolder As String
Dim Str As String
Dim a As Single
Dim sht As Worksheet
'Create a new worksheet and svae it to object named WS
Set WS = Sheets.Add
'Show dialog box and ask for a folder
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
'Save selected folder path to variable myfolder
myfolder = .SelectedItems(1) & "\"
End With
'Show inputbox and ask for search string to use
Str = Application.InputBox(prompt:="Search string:", Title:="Search all workbooks in a folder", Type:=2)
'Stop macro if string is nothing
If Str = "" Then Exit Sub
'Save text "Search string:" to cell A1 on worksheet WS
WS.Range("A1") = "Search string:"
'Save text in variable Str to cell B1 on worksheet WS
WS.Range("B1") = Str
'Save text "Path:" to cell A2 on worksheet WS
WS.Range("A2") = "Path:"
'Save text in variable myfolder to cell B2 on worksheet WS
WS.Range("B2") = myfolder
'Save text "Workbook" to cell A3 on worksheet WS
WS.Range("A3") = "Workbook"
'Save text "Worksheet" to cell B3 on worksheet WS
WS.Range("B3") = "Worksheet"
'Save text "Cell Address" to cell C3 on worksheet WS
WS.Range("C3") = "Cell Address"
'Save text "Link" to cell D3 on worksheet WS
WS.Range("D3") = "Link"
'Save 0 (zero) to variable a
a = 0
' The DIR function returns the first filename that matches the pathname specified in variable myfolder and save it to variable Value
Value = Dir(myfolder)
'Keep iterating until variable Value is nothing
Do Until Value = ""
'Check if Value is . or ..
If Value = "." Or Value = ".." Then
'If Value is not . or .. run these lines of code
Else
'Check If file names last three characters match xls or four last characters match xlsx or xlsm
If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
'Enable error handling
On Error Resume Next
'Open workbook based on path in variable folder and file name in variable Value using password zzzzzzzzzzzz
Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"
'Check if an error has occured
If Err.Number > 0 Then
'Save path to column A based on counter variable a
WS.Range("A4").Offset(a, 0).Value = Value
'Save text "Password protected" to column B based on counter variable a
WS.Range("B4").Offset(a, 0).Value = "Password protected"
'Add 1 to the number stored in variable a
a = a + 1
'Continue here if no error is returned
Else
'Disable error handling
On Error GoTo 0
'Go through all worksheets in active workbook
For Each sht In ActiveWorkbook.Worksheets
'Seach worksheet based on value in variable Str and save result to object c
Set c = sht.Cells.Find(Str, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
'Check if object c is not nothing
If Not c Is Nothing Then
'Save address based on range object c to variable firstAddress
firstAddress = c.Address
'Repet following lines between Do and Loop
Do
'Save file name to column A and row number based on variable a
WS.Range("A4").Offset(a, 0).Value = Value
'Save worksheet name to column B and row number based on variable a
WS.Range("B4").Offset(a, 0).Value = sht.Name
'Save cell address to column C and row number based on variable a
WS.Range("C4").Offset(a, 0).Value = c.Address
'Create hyperlink pointing to file name , worksheet name and cell address, show hyperlink text "Link"
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
sht.Name & "!" & c.Address, TextToDisplay:="Link"
'Increase count variable a with 1
a = a + 1
'Find next cell that matches and save to object c
Set c = sht.Cells.FindNext(c)
'Keep iterating until c is nothing and c is not equal to the first found cell in the same worksheet
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
'Continue with next worksheet
Next sht
End If
'Close workbook and do not save any changes made
Workbooks(Value).Close False
'Diable error handling
On Error GoTo 0
End If
End If
'Find next file
Value = Dir
'Keep iterating
Loop
'Change cell widths to fit text
Cells.EntireColumn.AutoFit
End Sub
Explaining the macro
The macro loops through all files in the given folder and if the file extension matches xls, xlsx or xlsm the workbook is opened.
If the workbook is password protected the macro outputs "Password protected" for that workbook.
If it successfully opens a workbook another loop is started that searches each worksheet in the workbook for the given search string.
Where to put the code?
- Copy macro (CTRL + c)
- Go to the VB Editor (Alt + F11)
- Press with left mouse button on "Insert" on the top menu.
- Press with left mouse button on "Module" to insert a code module to your workbook.
- Paste code to the module. (CTRL + v)
- Return to Excel.
- Save your workbook as a macro-enabled workbook (*.xlsm file).
If you don't the macro will be gone the next time you open the same workbook.
Example
Example, I have 5 workbooks in folder c:\temp\ containing many random numbers. The macro asks for the path and the search string.
It then creates this sheet.
Press with left mouse button on a link and excel loads the corresponding workbook and navigates to the specific cell.
Search other file extensions
Change the following line to whatever file extensions you want to look for.
If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
Search multiple strings in all workbooks in a folder
The following macro allows you to search all workbooks in a folder for multiple search strings.
'Name macro
Sub SearchWKBooks()
'Dimension variables and declare their data types
Dim WS As Worksheet
Dim myfolder As String
Dim Str As String
Dim a As Single
Dim sht As Worksheet
Dim RNG As Range
'Enable error handling
On Error Resume Next
'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)
'Disable error handling
On Error GoTo 0
'Show dialog box and ask for a folder
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
myfolder = .SelectedItems(1) & "\"
End With
'Add worksheet
Set WS = Sheets.Add
'Write "Path:" to cell A2
WS.Range("A2") = "Path:"
'Write contents of variable myfolder to cell B2
WS.Range("B2") = myfolder
'Write "Workbook" to cell A3
WS.Range("A3") = "Workbook"
'Write "Worksheet" to cell B3
WS.Range("B3") = "Worksheet"
'Write "Cell Address" to cell C3
WS.Range("C3") = "Cell Address"
'Write "Link" to cell D3
WS.Range("D3") = "Link"
'Write "Search string" to cell E3
WS.Range("E3") = "Search string"
'Save 0 (zero) to variable a
a = 0
'The DIR function returns the first filename that matches the pathname specified in variable myfolder and saves it to variable Value
Value = Dir(myfolder)
'Keep iterating through all files and folders until variable Value is empty
Do Until Value = ""
'Check if Value is . meaning current directory or .. meaning parent directory
If Value = "." Or Value = ".." Then
'Go here if Value is not . or ..
Else
'Check if last three characters match xls or last four characters match xlsx or xlsm
If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
'Enable error handling
On Error Resume Next
Open workbook using password "zzzzzzzzzzzz"
Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"
'Check if an error has occurred
If Err.Number > 0 Then
'Write contents of variable Value to cell A4 offset by number in variable a
WS.Range("A4").Offset(a, 0).Value = Value
'Write "Password protected" of variable Value to cell A4 offset by number in variable a
WS.Range("B4").Offset(a, 0).Value = "Password protected"
'Add 1 to number stored in variable a
a = a + 1
Else
'Disbale error handling
On Error GoTo 0
'Iterate through each worksheet in active workbook
For Each sht In ActiveWorkbook.Worksheets
'Iterate through all cells in variable RNG
For Each d In RNG
'Find cells in worksheet sht equal to value in cell d and save to range object variable c
Set c = sht.Cells.Find(d, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
'Check if range object variable c is populated
If Not c Is Nothing Then
'Save address of range object variable c to variable firstAddress
firstAddress = c.Address
'Do ... Loop through all cells in range object variable c
Do
'Save contents of variable Value to cell A4 offset by variable a
WS.Range("A4").Offset(a, 0).Value = Value
'Save worksheet name to cell B4 offset by variable a
WS.Range("B4").Offset(a, 0).Value = sht.Name
'Save cell address to cell C4 offset by variable a
WS.Range("C4").Offset(a, 0).Value = c.Address
'Create a hyperlink in cell D4
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
sht.Name & "!" & c.Address, TextToDisplay:="Link"
'Save cell value in variable d to column E and row based on count variable a
WS.Range("E4").Offset(a, 0).Value = d
'Add 1 to variable a
a = a + 1
'Go to next cell in range object variable c
Set c = sht.Cells.FindNext(c)
'Continue to loop as long as c is populated AND c is not equal to the first address stored in variable
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
Next d
Next sht
End If
Workbooks(Value).Close False
On Error GoTo 0
End If
End If
Value = Dir
Loop
Cells.EntireColumn.AutoFit
End Sub
How to use the macro
The macro asks for a cell range containing the search strings you want to look for. The next step is which folder to look in?
A new sheet is inserted and populated with data if values are found.
Search multiple strings and return corresponding value(s) on the same row
The macro demonstrated above looks for multiple search strings in all Excel workbooks in a folder and also returns a value on the same row.
Sub SearchWKBooks()
Dim WS As Worksheet
Dim myfolder As String
Dim Str As String
Dim a As Single
Dim sht As Worksheet
Dim RNG As Range
On Error Resume Next
'Show inputbox and ask for cell range containing search values
Set RNG = Application.InputBox(prompt:="Select a cell range containing search strings" _
, Title:="Select a range", Default:=ActiveCell.Address, Type:=8)
On Error GoTo 0
'Show inputbox and ask for cell offset
Str = Application.InputBox(prompt:="Cell Offset:", Title:="Offset", Type:=2)
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
myfolder = .SelectedItems(1) & "\"
End With
Set WS = Sheets.Add
WS.Range("A2") = "Path:"
WS.Range("B2") = myfolder
WS.Range("A3") = "Workbook"
WS.Range("B3") = "Worksheet"
WS.Range("C3") = "Cell Address"
WS.Range("D3") = "Link"
WS.Range("E3") = "Search string"
WS.Range("F3") = "Returned value"
a = 0
Value = Dir(myfolder)
Do Until Value = ""
If Value = "." Or Value = ".." Then
Else
If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
On Error Resume Next
Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"
If Err.Number > 0 Then
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = "Password protected"
a = a + 1
Else
On Error GoTo 0
For Each sht In ActiveWorkbook.Worksheets
For Each d In RNG
'MsgBox d
Set c = sht.Cells.Find(d, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not c Is Nothing Then
firstAddress = c.Address
Do
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = sht.Name
WS.Range("C4").Offset(a, 0).Value = c.Address
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
sht.Name & "!" & c.Address, TextToDisplay:="Link"
WS.Range("E4").Offset(a, 0).Value = d
'Save value from cell based on offset variable Str
WS.Range("F4").Offset(a, 0).Value = c.Offset(0, Str).Value
a = a + 1
Set c = sht.Cells.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
Next d
Next sht
End If
Workbooks(Value).Close False
On Error GoTo 0
End If
End If
Value = Dir
Loop
Cells.EntireColumn.AutoFit
End Sub
How to use
- Start macro (Alt+F8)
- Select the cell range containing search strings
- Press with left mouse button on OK button.
- Select how many cells to the right (positive numbers) or to the left (negative numbers) from the found value.
Example, 1 will return the adjacent value to the right.
Example, -2 will return the value from a cell two columns to the left of the found value. - Select the folder you want to use.
- Press with left mouse button on OK
The macro will now add a worksheet to your workbook and populate it with data if values are found.
The following article explains how to search a folder and subfolders:
Recommended articles
Search all workbooks in a folder and sub folders – VBA
This article demonstrates macros that allow you to search for a text string(s) in multiple worksheets and workbooks located in […]
This article demonstrates macros that allow you to search for a text string(s) in multiple worksheets and workbooks located in […]
Recommended reading:
Recommended articles
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
6. Search all workbooks in a folder and sub folders - VBA
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 section
- Search all workbooks in a folder and subfolders using one search string - VBA
- Search all workbooks in a folder and subfolders using multiple search strings - VBA
- Where to put the code?
6.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.
6.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
6.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.
6.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".
6.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
Copy data from workbooks in folder and subfolders
Table of Contents Copy data from workbooks in folder and subfolders Move data to workbooks Copy each sheet in active […]
Table of Contents Copy data from workbooks in folder and subfolders Move data to workbooks Copy each sheet in active […]
Search for a file in folder and subfolders – UDF
Table of Contents Search for a file in folder and sub folders - User Defined Function Search for a file […]
Table of Contents Search for a file in folder and sub folders - User Defined Function Search for a file […]
Working with FILES
Table of Contents Working with FILES Compare file names in two different folder locations and their sub folders Which Excel […]
Table of Contents Working with FILES Compare file names in two different folder locations and their sub folders Which Excel […]
Excel categories
134 Responses to “Search all workbooks in a folder”
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.
Excellent. Excellent job. The BEST of the BEST of the BEST.
Matt,
Thank you, I am happy you like it.
Is it possible to search a folder of workbooks, based on a cell within those workbooks, and collect a value from a different cell?
Kyle,
Yes, I believe it is possible. Send me an example workbook.
Tried using this and get a run-time error '91': object variable or With black variable not set. And it's this line of code: "Loop While Not c Is Nothing And c.Address firstAddress"
Any ideas why? thanks!
Cathy,
I made this macro in excel 2010, what is your version?
also using Excel 2010.
Cathy,
mac?
nope, just regular PC. thanks.
Cathy,
I found this: https://msdn.microsoft.com/en-us/library/aa264506(v=vs.60).aspx
but I have no clue.
No worries - thanks for looking!
Your Post has been very helpful, but I'm stuck on something. I've modified your code to fit my needs, but the problem is that my files I am pulling from have 31 pages, and I need the Macro to only pull from the first page. Is there code to do this? All 31 pages have the same "search" words.
Sub SearchWKBooks() Dim WS As Worksheet Dim myfolder As String Dim Str As String Dim a As Single Dim sht As Worksheet Set WS = Sheets.Add With Application.FileDialog(msoFileDialogFolderPicker) .Show myfolder = .SelectedItems(1) & "\" End With Str = Application.InputBox(prompt:="Search string:", Title:="Search all workbooks in a folder", Type:=2) If Str = "" Then Exit Sub WS.Range("A2") = "Path:" WS.Range("B2") = myfolder WS.Range("A3") = "Client" WS.Range("B3") = "Job #" WS.Range("C3") = "Labor" WS.Range("D3") = "Hours" WS.Range("E3") = "Materials" WS.Range("F3") = "ShopMtls" WS.Range("G3") = "Subs" WS.Range("H3") = "Other/Trash" WS.Range("I3") = "Job Cost" WS.Range("J3") = "Sell Price" WS.Range("K3") = "Profit $" WS.Range("L3") = "Profit %" WS.Range("M3") = "Link" a = 0 Value = Dir(myfolder) Do Until Value = "" If Value = "." Or Value = ".." Then Else If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then On Error Resume Next Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz" If Err.Number > 0 Then WS.Range("A4").Offset(a, 0).Value = Value WS.Range("B4").Offset(a, 0).Value = "Password protected" a = a + 1 Else On Error GoTo 0 For Each sht In ActiveWorkbook.Worksheets Set c = sht.Cells.Find(Str, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext) If Not c Is Nothing Then firstAddress = c.Address Do WS.Range("A4").Offset(a, 0).Value = Value WS.Range("B4").Offset(a, 0).Value = Range("K1") WS.Range("C4").Offset(a, 0).Value = Range("B36") WS.Range("D4").Offset(a, 0).Value = Range("c36") WS.Range("E4").Offset(a, 0).Value = Range("d36") WS.Range("F4").Offset(a, 0).Value = Range("e36") WS.Range("G4").Offset(a, 0).Value = Range("f36") WS.Range("H4").Offset(a, 0).Value = Range("g36") WS.Range("I4").Offset(a, 0).Value = Range("h36") WS.Range("J4").Offset(a, 0).Value = Range("M36") WS.Range("K4").Offset(a, 0).Value = Range("M43") WS.Range("L4").Offset(a, 0).Value = Range("J46") WS.Hyperlinks.Add Anchor:=WS.Range("M4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _ sht.Name & "!" & c.Address, TextToDisplay:="Link" a = a + 1 Set c = sht.Cells.FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End If Next sht End If Workbooks(Value).Close False On Error GoTo 0 End If End If Value = Dir Loop Cells.EntireColumn.AutoFit End SubInstead of pages I mean "worksheets"
Jonathan J,
Make these changes to my macro.
Change this line:
to this:
Set sht = Worksheets("Sheet1")Change Sheet1 to the sheet name you want to use.
Remove this line:
Next sht
Hai
Your code is very helpful for my requirement
if possible . kindly change the code to find 100 numbers from the folder (ie instead of giving each number input 100 numbers)
I have a folder of excel files that are named by no type of order and I was hoping to find a way to search through all of them and point out the ones that have a value greater than one I specific only in a specific column. So search through all files and tell me the ones that have a value greater than 6 in column C. Is this possible with minimal changes to this macro ? Cheers
[…] they accomplish the majority of the task that I want my macro to accomplish. Here's the first one Search all workbooks in a folder | Get Digital Help - Microsoft Excel resource, this macro creates a new worksheet that houses info on which cells contain the string that you […]
This code looks like it might be fantastically helpful to me and I appreciate your sharing it. I have got a syntax error at line 44:
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
I am using Excel 2013 and wonder if this might be the problem? (I am not experienced in VB). Many thanks
[…] a VBA module that will perform a text search in all workbooks in a particular folder. Blog post: Search all workbooks in a folder | Get Digital Help - Microsoft Excel resource Example workbook: […]
hi thanks for the great macro really makes a hard job much easier, can this be made to search subfolders as well?
thanks
rusl
rusl cato,
read this post:
Search all workbooks in a folder and sub folders
[…] (adsbygoogle = window.adsbygoogle || []).push({}); I am looking for a way to search the entire worksheet of multiple spreadsheets within a single folder for values greater than 100. If cells values of greater than 100 are found, then these will be listed in a worksheet/tab with file directory, worksheet name, cell address, cell value, and hyperlink to that cell. I have been able to find the code below which was very helpful for searching for a particular text strings. However, it is values (I think they are integers!?) that I need to search for (that are greater than 100). My VBA skills are basic and therefore not sure where to start. Any hints/tips would be great or a modification of the code below would be fantastic. Thanks for your help! https://www.get-digital-help.com/2014/01/08/search-all-workbooks-in-a-folder/ […]
Thank you SO much for this code! It is a lifesaver for a monthly process that occasionally results in my having to look for an error in 80+ Excel files!
Elaine Weigle,
I am happy you like it!
[…] You might want to give a look at this. Search all workbooks in a folder | Get Digital Help - Microsoft Excel resource […]
[…] there you might want to give a look at that.Search all workbooks in a folder | Get Digital Help - Microsoft Excel resource […]
Is there a way to make the search look in subdirectories as well
Matt Durbin,
read this post:
Search all workbooks in a folder and sub folders
[…] for some text but i don't want to open all these files one by one. I chanced across this website Search all workbooks in a folder | Get Digital Help - Microsoft Excel resource which has got the code to supposedly fulfill my needs. However, it does not work. The line […]
[…] Search all workbooks in a folder is a popular post, I am happy so many find it useful. […]
[…] 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!
Hi! The file seems to be offline. Cant get it!
Any help? Other places where it is hosted?
Olaf,
The file works here. I emailed you the file.
Sir, This is very helpful topic. Here is one question or I want solution that I have one excel file how has the values in Column B. I Want it search one by one name in the folder worksheets. Can it be possible this.
Is there any way to adapt this code to repeat itself and find a list of data rather than just one specific "Str" and return all results on the same worksheet?
Bravo. Super macro - thank you for publishing it. Windows Explorer ("WE") with indexing option could not handle contents of my set of Excel workbook files. "WE" search was listing only some files but not all of them.
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.
This looks great for what I am looking for. Can this code search workbooks with folders for duplicate reference numbers and then highlight those duplicates on a new worksheet perhaps
John,
No, the macro can´t do that, as it is. Can you describe in greater detail what you want to do?
Hi, is there any way to change it to reference a cell for the path and another for the search string?
Subscribed. This code cut my workload for the week by 90%.
I can't even begin to express my gratitude for this.
stefan
I am really happy to hear that, thank you for commenting.
Is there a way to adapt this code if all workBOOKS I am searching are password protected and all have the same password? I'd like to assure that the workBOOKS are re-protected by the same password after the search. Many thanks!
Sali
Change "zzzzzzzzzzzz" to your "password".
Excellent and The Best. Save me from hours of manual checking of raw data
helmi
Thank you.
Hey
Nice job, but for me it doesnt work.
I set the folder and the search key, it opens all the files but will not return any answer.
I use office 2010
Since a long time I was looking for the same type of code and
has matched to my requirement. I appreciate your work and knowledge.
I had also posted this in one of the forum but have not received any reply till date.
In case if possible in your code i.e the top first code if I
can search in all the files but in only one sheet say Sheet1.
I did see the code by Jonathan. But since there are lots of changes and I am not from the VBA background so I am unable to
understand the code.
As mentioned when I changed the line in your code it gave the error of Next.
I will be very thankful if you can help on this issue.
Thanks again
Veerat
Hi,
I am trying to search for a 'number' using this code. It shows the right sheet where that number is , but since I am trying to open sheets with the same 'number'
For example: sheet1 has 1,2,3,4,6,7,8
sheet2- Cell A1 = any of these numbers. SO, when I run the code it shows that number exists in sheet 1 but doesn't show sheet 2 which is what I am trying to reference the code to. Also, how can I replace the searched quantity to look at a specific cell in the worksheet?
I tried set sht = sheet("Sheet1") but when I do that it doesn't read the value but read its when all sheet are being checked for
hi, your marco is rly great, but is it possible to search more than two single strings at the same time? thanks.
Excellent, thank you.
would this work for Excel on a Mac?
Thanks for creating this code, which makes me hope, it can be the basis of something I'm looking for.
I would need to make a search of 5000 different values in multiple workbooks, located in a folder/subfolders. I didn't yet find any similar solution, however, the desired search result is more complex.
The point would be to find specific data, located in column A for example, which contains 5000 rows of data. In column B I would like to make the name of containing workbook written for every row of column A.
It is also possible, the column A data is not listed in any workbooks, so it should be left empty or any static text, like: "Not found".
Does anybody think, creating this code is possible?
Many thanks for any help!
Daniel
Brilliant! Thank you.
Thanks a lot!
Very new to computers and I have Excel 2003 and the MS update which does load your xlms file. The result after running your file says no files are in my folder, yet it has a great many xls files.
Can you help please.
Regards,
Kate
Any way to make this work on Mac? Love the whole idea, just need to use Mac. Thanks.
Chris,
I don´t own a mac, I can´t help you out.
Trying to help my daughter - this could be extremely useful in her job; however, it seems to only find results on the first sheet. She must search 20 to 30 Excel files, each with varying number of worksheets. The data she needs to find could be in any one of the worksheets, not just the first one. So far, this macro finds the data on the first sheet, not on any others. Can this be modified to search all the sheets in a workbook before going to the next?
This is such a terrific macro and will help save her hours of work if she can use it over multiple sheets within multiple workbooks. Thank you so much for your willingness to share your knowledge and expertise.
Chris,
Thank you.
It does search all the sheets in a workbook.
What excel version are you using?
I run the macro and it stops after open the first file in the folder and shows "This workbook contains links to other data sources" and then when I press with left mouse button on "no update links" the macro stops. Could you help me please? I'm not sure what is causing it?
Thanks!
Hey, thanks for the code, is there a way to change it so instead of prompting for the folder you can have it set to a certain location?
and also can you have it so it doesn't have to open a new worksheet each time?
Also does this work for words too, cant seem to get it to work searching for a key word in a cell
Correction, Does work for works but only if you get the whole cell right, can this be changed so it shows if any cell has the search word?
I want to get the pdf file base on the data in excel and save the pdf file in the other folder. can you help ?
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.
This is just Perfect i was looking for. Thanks a ton sir. Much Respect :)
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 :)
This macro is awesome! Thanks so much!
I see that this only works if the cell contents match exactly. Can anyone tell me how to modify the code so that it returns a result even the cell contents don't match exactly? For example, I enter search string 123.45a67 and the folder i am searching in has results such as 123:123.45a67. I want this to come up as a result also.
Thank you for this incredibly helpful macro!
I also would like to be able to search for contents "containing" only partial cell content. EX: search for 1234 and get cells that contain 123456
Thanks!
Hi Ron or Oscar, Just wondering if you had any luck with a partial match. As this is excatley what im looking for. Thank you again for an amazing code Oscar. James
Macro is great but
i'm getting is message
"search.xlsm is already open. reopening will cause any changes you made to be discarded. do you want to reopen search.xlsm"
after this workbook is closed
Verinder Singh
You probably have saved your workbook in the same folder as you are searching in.
Good day,
the code is working great!! Thanks :)
but I have another question. It is possible that in column E and F displays values from columns U and V from the workbooks. For example if am searching for a value 1234 and it is found in Book1 Cell A14 that displays also value fm cell U14 and V14?
many thanks
Hi Oscar,
This looks like this will be perfect for what I need. My only question is, instead of searching a local file can the macro search documents stored in a folder that is stored on a SharePoint?
Many thanks!
Steven Knightley
If the workbook is allowed to open excel workbooks stored on a sharepoint it should work fine.
Very Good code. Is it possible to have a list of all the key search words (search strings) and then run the macro for all the key words at once with the output in single sheet for all. I have like a list of 30 items that I want to search in a dump every hour, so can't really use this macro manually 30 times every hour. I hope you can help. Thanks in advance.
Also, it is feasible to search parts of values in a cell instead of the entire value in a cell as currently this macro gives result only if the entire search string is available in a cell.
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
I am looking for a VBA code which searhc excel file name from a folder based on name mentioned in sample file name and email to persons.
Example:-
I have a excel file which columns (A1 as Customer Name , B1 as email ID
VBA code to search name from cloumn A and same excel name file in a folder and email to mentioned email ID mentioned in B cloumn.
Please help to share the VBA code.
[email protected]
Hi,
I have tried and it was successful before. But now, I has problem
How to solve this problem?
"Runtime error 52
Bad file name or number". Thanks for the topic
Regards!
Truong,
I am not sure what is wrong but I found this:
https://msdn.microsoft.com/VBA/Language-Reference-VBA/articles/bad-file-name-or-number-error-52
Hi
I am grateful for sharing the macro. It works great.
For now VBA is for me unknown world. Is it possible to adapt it for search csv files?
Regards.
Hi,
This is a very awesome vba. I'm wondering if it's possible to only loop until the first match is found, then move to the next search string?
Regards.
Great bit of VBA code. Thank you VERY much. You generously providing this saved me quite a bit of time and effort.
One issue I am having is that the links open the "found" file, but give me a "invalid cell address" message. This may correlate to the Excel version in that it works fine for v. 2010, but there appears to be a hyperlink address issue with v. 2007. As far as I know, there is no different is how these 2 Excel version use hypertext links to other files & cells. Any insight would be welcome.
dw,
Which vba line is highlighted when the error occurs?
The macro fully executes properly and the issue only occurs when the hyperlink to the found cells is used.
I did a bit more looking and found that the hyperlink address did not include single quotes around the sheet name. It appears that if the sheet name contains spaces Excel need single quotes around the sheet name.
Again, thank you VERY much for your excellent work and assistance.
dw,
You are right, there seems to be a problem with worksheet names containing one or more space characters.
Here is a macro that works:
Search-multiple-workbooks-in-a-folderv3.xlsm
The problem is this line:
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:=_ sht.Name & "!" & c.Address, TextToDisplay:="Link"change it to:
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:=_ "'" & sht.Name & "'!" & c.Address, TextToDisplay:="Link"hi, I installed your code today, and it was running, but then i realized the Excel files i need are in binary format . . xlsb. I changed the macro to look for xlsb but not it runs, but stalls (and does nothing) after creating a target worksheet.
thoughts?
Jeffrey R Green,
Can you provide the vba line you changed?
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
Hi, the code is fantastic, however I was hoping you may be able to help me narrow this down. Can the folder location be preset, as in it only looks in the same folder each time ? that way you don't need to select the folder, just the string? I've tried tweaking the code but it always goes back to the original selection. Also, is there a way to pick up the string from a range, rather than a opening a inputbox?
Michael A
Can the folder location be preset, as in it only looks in the same folder each time ? that way you don't need to select the folder, just the string?
Yes, delete the following lines:
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
myfolder = .SelectedItems(1) & "\"
End With
and replace with:
myfolder = "c:\yourfolder\"
Also, is there a way to pick up the string from a range, rather than a opening a inputbox?
myfolder = Worksheets("Sheet1").Range("A1").Value
Hi I have a question I have adjusted the code to my needs but i am running into a issue with some certain key numbers. it is like once it search for one of the certain key numbers/data, it does find the number but it never breaks out of the loop where it is copying the number of said category of number onto the spreadsheet.
Sub SearchWKBooks()
Dim WS As Worksheet
Dim myfolder As String
Dim Str As String
Dim a As Single
Dim Count As Single
Dim Found As Single
Dim sht As Worksheet
Dim Find As Range
Dim Val As Range
Dim test As String
'prompt the user to select the cells of what data to look for
On Error Resume Next
Set Find = Application.InputBox(Prompt:="Select a cell range containing search data(Example: $C$3:$C$20)" _
, Title:="Select the cell data range!", Default:=ActiveCell.Address, Type:=8)
If Err.Number = 424 Then
Exit Sub
End If
'prompt the user to select the cell with the value catagory to match up with
Set Val = Application.InputBox(Prompt:="Select a Catagory(Example: Paid In Full)" _
, Title:="Select a Cell Containing the Catogry to look for!", Default:=ActiveCell.Address, Type:=8)
If Err.Number = 424 Then
Exit Sub
End If
test = InputBox(Prompt:="Please enter password if the workbook is password protected, If there is no password please press ok" _
, Title:="Password", Default:="password")
If test = vbNullString Then
Exit Sub
End If
On Error GoTo 0
'prompt the user to select the file folder to run through the find the data in the list
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = True Then
myfolder = .SelectedItems(1) & "\"
Else
Exit Sub
End If
End With
'Creating a new spread sheet to display the Data that was found along with location and link
Set WS = Sheets.Add
WS.Range("A2") = "Path:"
WS.Range("B2") = myfolder
WS.Range("A3") = "Workbook"
WS.Range("B3") = "Worksheet"
WS.Range("C3") = "Cell Address"
WS.Range("D3") = "Link"
WS.Range("E3") = "Search string"
WS.Range("F3") = "Catagory: " & Val
'set the counters to 0 or 1
a = 0
Count = 1
'Turning off the application updaters and events to speed up the process of the search without taking long.
'Application.StatusBar = False
'Application.ScreenUpdating = False
'Application.Calculation = xlCalculationManual
'Application.EnableEvents = False
'opening the file in the folder location and running the data through each spread sheet to see if the data is there and report the finding of found or not.
Value = Dir(myfolder)
Do Until Value = ""
If Value = "." Or Value = ".." Then
Else
If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
On Error Resume Next
Workbooks.Open Filename:=myfolder & Value, Password:=test
If Err.Number > 0 Then
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = "Password protected"
a = a + 1
Else
On Error GoTo 0
For Each d In Find
For Each sht In ActiveWorkbook.Worksheets
Set c = sht.Cells.Find(d, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
' check the c cell to see if the object match with the data that is currently being looked for and if found check the value/catagory if it is the correct data set
If Not c Is Nothing Then
firstAddress = c.Address
Do
If UCase(Val) = UCase(c.Offset(0, 1).Value) Then
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = sht.Name
WS.Range("C4").Offset(a, 0).Value = c.Address
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
"'" & sht.Name & "!" & c.Address, TextToDisplay:="Link"
WS.Range("E4").Offset(a, 0).Value = d
WS.Range("F4").Offset(a, 0).Value = c.Offset(0, 1).Value
a = a + 1
Found = 1
Set c = sht.Cells.FindNext(c)
End If
Loop While Not c Is Nothing And c.Address firstAddress
End If
' report the Data was not found after reaching the last spread sheet and checking if found was change to 1 if not then report not found.
If Count = ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count).Index Then
If Found = 1 Then
Else
WS.Range("A4").Offset(a, 0).Value = "could not find"
WS.Range("B4").Offset(a, 0).Value = "please double check the number"
WS.Range("C4").Offset(a, 0).Value = "N/A"
WS.Range("D4").Offset(a, 0).Value = "N/A"
WS.Range("E4").Offset(a, 0).Value = d
WS.Range("F4").Offset(a, 0).Value = "None"
a = a + 1
End If
End If
Count = Count + 1
Next sht
Count = 1
Found = 0
Next d
End If
Workbooks(Value).Close False
On Error GoTo 0
End If
End If
Value = Dir
Loop
'turn back on the status update screen calculation and events
'Application.StatusBar = True
'Application.ScreenUpdating = True
'Application.Calculation = xlCalculationAuto
'Application.EnableEvents = True
Cells.EntireColumn.AutoFit
End Sub
Hello I appriciate what your code does but I made some modifications to it and I am running into a issue where the code stays inside the loop and never breaks out after finding the key numbers I am looking for.
[VB 1="vbnet" Language=","]
Sub SearchWKBooks()
Dim WS As Worksheet
Dim myfolder As String
Dim Str As String
Dim a As Single
Dim Count As Single
Dim Found As Single
Dim sht As Worksheet
Dim Find As Range
Dim Val As Range
Dim test As String
'prompt the user to select the cells of what data to look for
On Error Resume Next
Set Find = Application.InputBox(Prompt:="Select a cell range containing search data(Example: $C$3:$C$20)" _
, Title:="Select the cell data range!", Default:=ActiveCell.Address, Type:=8)
If Err.Number = 424 Then
Exit Sub
End If
'prompt the user to select the cell with the value catagory to match up with
Set Val = Application.InputBox(Prompt:="Select a Catagory(Example: Paid In Full)" _
, Title:="Select a Cell Containing the Catogry to look for!", Default:=ActiveCell.Address, Type:=8)
If Err.Number = 424 Then
Exit Sub
End If
test = InputBox(Prompt:="Please enter password if the workbook is password protected, If there is no password please press ok" _
, Title:="Password", Default:="password")
If test = vbNullString Then
Exit Sub
End If
On Error GoTo 0
'prompt the user to select the file folder to run through the find the data in the list
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = True Then
myfolder = .SelectedItems(1) & "\"
Else
Exit Sub
End If
End With
'Creating a new spread sheet to display the Data that was found along with location and link
Set WS = Sheets.Add
WS.Range("A2") = "Path:"
WS.Range("B2") = myfolder
WS.Range("A3") = "Workbook"
WS.Range("B3") = "Worksheet"
WS.Range("C3") = "Cell Address"
WS.Range("D3") = "Link"
WS.Range("E3") = "Search string"
WS.Range("F3") = "Catagory: " & Val
'set the counters to 0 or 1
a = 0
Count = 1
'Turning off the application updaters and events to speed up the process of the search without taking long.
'Application.StatusBar = False
'Application.ScreenUpdating = False
'Application.Calculation = xlCalculationManual
'Application.EnableEvents = False
'opening the file in the folder location and running the data through each spread sheet to see if the data is there and report the finding of found or not.
Value = Dir(myfolder)
Do Until Value = ""
If Value = "." Or Value = ".." Then
Else
If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
On Error Resume Next
Workbooks.Open Filename:=myfolder & Value, Password:=test
If Err.Number > 0 Then
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = "Password protected"
a = a + 1
Else
On Error GoTo 0
For Each d In Find
For Each sht In ActiveWorkbook.Worksheets
Set c = sht.Cells.Find(d, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
' check the c cell to see if the object match with the data that is currently being looked for and if found check the value/catagory if it is the correct data set
If Not c Is Nothing Then
firstAddress = c.Address
Do
If UCase(Val) = UCase(c.Offset(0, 1).Value) Then
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = sht.Name
WS.Range("C4").Offset(a, 0).Value = c.Address
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
"'" & sht.Name & "!" & c.Address, TextToDisplay:="Link"
WS.Range("E4").Offset(a, 0).Value = d
WS.Range("F4").Offset(a, 0).Value = c.Offset(0, 1).Value
a = a + 1
Found = 1
Set c = sht.Cells.FindNext(c)
End If
Loop While Not c Is Nothing And c.Address firstAddress
End If
' report the Data was not found after reaching the last spread sheet and checking if found was change to 1 if not then report not found.
If Count = ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count).Index Then
If Found = 1 Then
Else
WS.Range("A4").Offset(a, 0).Value = "could not find"
WS.Range("B4").Offset(a, 0).Value = "please double check number"
WS.Range("C4").Offset(a, 0).Value = "N/A"
WS.Range("D4").Offset(a, 0).Value = "N/A"
WS.Range("E4").Offset(a, 0).Value = d
WS.Range("F4").Offset(a, 0).Value = "None"
a = a + 1
End If
End If
Count = Count + 1
Next sht
Count = 1
Found = 0
Next d
End If
Workbooks(Value).Close False
On Error GoTo 0
End If
End If
Value = Dir
Loop
'turn back on the status update screen calculation and events
'Application.StatusBar = True
'Application.ScreenUpdating = True
'Application.Calculation = xlCalculationAuto
'Application.EnableEvents = True
Cells.EntireColumn.AutoFit
End Sub
[/vb]
Hello I appriciate what your code does but I made some modifications to it and I am running into a issue where the code stays inside the loop and never breaks out after finding the key numbers I am looking for.
VB 1="vbnet" Language=","
Sub SearchWKBooks()
Dim WS As Worksheet
Dim myfolder As String
Dim Str As String
Dim a As Single
Dim Count As Single
Dim Found As Single
Dim sht As Worksheet
Dim Find As Range
Dim Val As Range
Dim test As String
'prompt the user to select the cells of what data to look for
On Error Resume Next
Set Find = Application.InputBox(Prompt:="Select a cell range containing search data(Example: $C$3:$C$20)" _
, Title:="Select the cell data range!", Default:=ActiveCell.Address, Type:=8)
If Err.Number = 424 Then
Exit Sub
End If
'prompt the user to select the cell with the value catagory to match up with
Set Val = Application.InputBox(Prompt:="Select a Catagory(Example: Paid In Full)" _
, Title:="Select a Cell Containing the Catogry to look for!", Default:=ActiveCell.Address, Type:=8)
If Err.Number = 424 Then
Exit Sub
End If
test = InputBox(Prompt:="Please enter password if the workbook is password protected, If there is no password please press ok" _
, Title:="Password", Default:="password")
If test = vbNullString Then
Exit Sub
End If
On Error GoTo 0
'prompt the user to select the file folder to run through the find the data in the list
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = True Then
myfolder = .SelectedItems(1) & "\"
Else
Exit Sub
End If
End With
'Creating a new spread sheet to display the Data that was found along with location and link
Set WS = Sheets.Add
WS.Range("A2") = "Path:"
WS.Range("B2") = myfolder
WS.Range("A3") = "Workbook"
WS.Range("B3") = "Worksheet"
WS.Range("C3") = "Cell Address"
WS.Range("D3") = "Link"
WS.Range("E3") = "Search string"
WS.Range("F3") = "Catagory: " & Val
'set the counters to 0 or 1
a = 0
Count = 1
'Turning off the application updaters and events to speed up the process of the search without taking long.
'Application.StatusBar = False
'Application.ScreenUpdating = False
'Application.Calculation = xlCalculationManual
'Application.EnableEvents = False
'opening the file in the folder location and running the data through each spread sheet to see if the data is there and report the finding of found or not.
Value = Dir(myfolder)
Do Until Value = ""
If Value = "." Or Value = ".." Then
Else
If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
On Error Resume Next
Workbooks.Open Filename:=myfolder & Value, Password:=test
If Err.Number > 0 Then
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = "Password protected"
a = a + 1
Else
On Error GoTo 0
For Each d In Find
For Each sht In ActiveWorkbook.Worksheets
Set c = sht.Cells.Find(d, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
' check the c cell to see if the object match with the data that is currently being looked for and if found check the value/catagory if it is the correct data set
If Not c Is Nothing Then
firstAddress = c.Address
Do
If UCase(Val) = UCase(c.Offset(0, 1).Value) Then
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = sht.Name
WS.Range("C4").Offset(a, 0).Value = c.Address
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
"'" & sht.Name & "!" & c.Address, TextToDisplay:="Link"
WS.Range("E4").Offset(a, 0).Value = d
WS.Range("F4").Offset(a, 0).Value = c.Offset(0, 1).Value
a = a + 1
Found = 1
Set c = sht.Cells.FindNext(c)
End If
Loop While Not c Is Nothing And c.Address firstAddress
End If
' report the Data was not found after reaching the last spread sheet and checking if found was change to 1 if not then report not found.
If Count = ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count).Index Then
If Found = 1 Then
Else
WS.Range("A4").Offset(a, 0).Value = "could not find"
WS.Range("B4").Offset(a, 0).Value = "please double check the account number or account"
WS.Range("C4").Offset(a, 0).Value = "N/A"
WS.Range("D4").Offset(a, 0).Value = "N/A"
WS.Range("E4").Offset(a, 0).Value = d
WS.Range("F4").Offset(a, 0).Value = "None"
a = a + 1
End If
End If
Count = Count + 1
Next sht
Count = 1
Found = 0
Next d
End If
Workbooks(Value).Close False
On Error GoTo 0
End If
End If
Value = Dir
Loop
'turn back on the status update screen calculation and events
'Application.StatusBar = True
'Application.ScreenUpdating = True
'Application.Calculation = xlCalculationAuto
'Application.EnableEvents = True
Cells.EntireColumn.AutoFit
End Sub
/vb
my apologies so it was just delayed on posting thought it was my computer... but i found a work around on the issue I just needed to include another if statement before the the matching of the value with a single counter check to force the find next c
brent
I am happy you got it working.
Brent, can you please share the modified code. As i too want to report the found or not found cases. Appreciate the help.
Oscar,
On your May 21, 2014 at 12:29 pm response to user Jonathan J, after applying the recommended changes you advised, the Worksheet (cell C4), Cell Address (D4), and Link (F4) no longer populate after the macro runs. Can you please help me out with this?
Hi
Is it possible to add a range in the workbooks? When I run it I get the result that the search word is repoted in the entire row
Cell Address
$B$1
$C$1
$D$1
$E$1
$F$1
$G$1
$H$1
$I$1
$J$1
$K$1
$L$1
$M$1
$N$1
$O$1
$P$1
$Q$1
$R$1
$S$1
$T$1
$U$1
$V$1
$W$1
$X$1
$Y$1
$Z$1
$AA$1
$AB$1
............
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
It's crazy. I have 75 excel files; each with multiple worksheets. This thing ROCKS on most files... totally ignores others. I get no errors. I've checked that the "search words" are in the bad files so that the macro can find it. I've renamed the bad files just in case something was wonky in the filename. I've rerun the macro multiple times; it is very consistent at missing the same files. I've created a clean folder with two files; one file that I know will work, the file other I know will not work... still ignores the same file.
I've also checked the file properties of the "bad" files. Unless I'm missing something they are the same. Got any ideas?
Ray
Thank you for "Search multiple strings and return corresponding value(s) on the same row". I use it all the time. I look for same string across different workbooks in multiple locations to sought values that are in different columns in each workbook (i.e. I am looking for QWERTY in workbook 1 to return value from column A, in workbook 2 value from column G, in workbook 3 value from column D etc.).
Would you be able to adjust the code for a N number of static searches where paths to workbooks and offset variables are fed from range in spreadsheet (similar to N numbers of search strings), i.e. in A1 path to workbook 1, B1 - where you manually input offset number, A2 path to workbook 2, B2 - manual input for offset column etc, looped until there is no path to workbook?
Oscar thanks a lot. You rock
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
Hey Oscar,
Great macro! Excactly what I was looking for. Very nice extra: the links in the outputsheet to directly jump to the results. This macro saves me a lot of time and effort every month. And maybe more important: the macro won't miss any match and I might when I search manually!
Thank you very much!
Naar.
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?
Hiya, I'm really glad I've found this information. Today bloggers publish just about gossips and internet and this is actually frustrating.
A good site with exciting content, this is what I
need. Thanks for keeping this website, I'll be visiting it.
Do you do newsletters? Can not find it.
Dear Mr. Oscar:
I was looking for this solution since ages! Excellent!!!
Is it possible to show value instead of link? How?
Thank you.
Welcome, Mister
The method is very useful and works great
But I have a problem
How to return multiple offset values 1 & 2 & 3
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?
Hi There
Thank you for the Great piece of work
can I run "Search-multiple-workbooks-in-a-folder.xlsm"
and have it leave all the files open instead of it closing them all?
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.
Oscar, Thanks a lot for this. It is what I have been looking for BUT I was hoping that after the string is found, How can I add a line in the code to "Entire row copy" and paste it to the output after the column containing the "Link"?
Thanks,
Kasango
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 ..