Search a string in multiple workbooks using vba excel – debugvba.com

search string vba excel file

Objective

To search a string in multiple excel files and save output in a file.

Approach

Here we wanted to search string in multiple excel files which are placed in a folder. Source folder can have any type of file, but we shall consider only excel file. So, to do so we have looped through each file in the folder one by one. We begin with opening a file, and then we search through each worksheet of that file, if the file contains the string which we are searching, then an entry will be made to sheet 1 of macro file.

We had tested this code in below shown folder, this folder has 2 excel files and 1 word document, we can see that in the image below.

 

search string vba excel file

The macro file which contains this code has a sheet named as “sheet1” , there are 2 columns (1) String (2) File Name, we can see them in the image below.

 

search string vba excel file

 

In the code below we have defined a variable called SearchString which contains the string “samplestring”. We have created a variable called SourceFolder which contains the path of folder which has source files.

Then we check if this folder exists or not. If this folder does not exist, then code will exit. If folder exists, then code will execute further. Then we loop through each file in the folder. We open a file and check if the search string is found inside file or not, if it is found then an entry will be made to sheet 1 of macro file. We shall search each of the worksheet of the opened workbook. The macro file will have string name in column A and file name along with path will be shown in column B.

Once we have gone through an excel file, we close it and then move on to next excel file.

The macro file will have one populated row in sheet1, which will contain only headers as shown below.

Code

Sub SearchInMultipleFiles()

Dim oFileSys As Object
Dim SourceFolder As String
Set oFileSys = CreateObject("Scripting.FileSystemObject")
Dim SearchString As String
Dim FindString As Range

SearchString = "samplestring"

'Path of the folder where excel files are locted
SourceFolder = "C:\Users\myusername\Desktop\macro\a\"

'Check if source folder exists
If oFileSys.FolderExists(SourceFolder) = True Then

'Looping through each file in the source folder
For Each SourceExcelfile In oFileSys.GetFolder(SourceFolder).Files

'Check for importing only excel files
If SourceExcelfile.Type = "Microsoft Excel Worksheet" Then

'Open source file one by one
Workbooks.Open Filename:=SourceExcelfile

'Loop thorugh each sheet and finf the string in entire sheet
For Each sh In ActiveWorkbook.Sheets
Set FindString = sh.Cells.Find(What:=SearchString, LookIn:=xlValues, LookAt:=xlWhole)
LastRow = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row

'Enter the results in sheet 1 of macro file
If Not FindString Is Nothing Then
ThisWorkbook.Sheets("sheet1").Range("A" & LastRow + 1).Value = SearchString
ThisWorkbook.Sheets("sheet1").Range("B" & LastRow + 1).Value = SourceExcelfile

'Close currently opened source file
Workbooks(SourceExcelfile.Name).Close

Exit For
End If

Next sh
End If


'Move to next source file
Next SourceExcelfile
Else
MsgBox "Source folder does not exist"
End If
Set oFileSys = Nothing

End Sub

 

In the image below we can see the output in the macro file.

 

search string vba excel file

Post you may like

Save the output of command prompt in a text or csv file using VBA Excel

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.range.find