To search a string in multiple excel files and save output in a file.
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.
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.
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.
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.
Post you may like