Author: Oscar Cronquist Article last updated on September 02, 2020


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.

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 click 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 .. execute 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

Back to top

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.

Back to top

Where to put the code?

  1. Copy macro (CTRL + c)
  2. Go to the VB Editor (Alt + F11)
  3. Click "Insert" on the top menu.
  4. Click "Module" to insert a code module to your workbook.
  5. Paste code to the module. (CTRL + v)
  6. Return to Excel.
  7. 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.

Back to top

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.

Search multiple workbooks in a folder - result sheet

Click a link and excel loads the corresponding workbook and navigates to the specific cell.

Back to top

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

Back to top

Back to top

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.

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

'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


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"

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

                    'Iterate through all cells in variable RNG
                    For Each d In RNG

                        'Find cells in worksheet sht equal to value in cell 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"

                                'Save cell value in variable d to column E and row based on count variable a
                                WS.Range("E4").Offset(a, 0).Value = d
                                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 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

  1. Start macro (Alt+F8)
  2. Select the cell range containing search strings
  3. Click OK button.
  4. 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.
  5. Select the folder you want to use.
  6. Click OK

The macro will now add a worksheet to your workbook and populate it with data if values are found.

Back to top

Back to top

The following article explains how to search a folder and subfolders:

Search all workbooks in a folder and sub folders

Search all workbooks in a folder is a popular post, I am happy so many find it useful. rusl cato […]

Search all workbooks in a folder and sub folders

Recommended reading:

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 […]

Open Excel files in a folder [VBA]