Search text in your excel file or sheet in VB.net

The following code snippet shows how we can search some text in your excel sheet.

Private Sub SearchText()
        Dim oXL As Excel.Application
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Try
            Dim File_name As String = "C:\test.xls"
            oXL = CreateObject("Excel.Application")
            oWB = oXL.Workbooks.Open(File_name)
            oSheet = oWB.Worksheets(1)
 
            Dim oRng As Excel.Range = GetSpecifiedRange("get", oSheet)
            If oRng IsNot Nothing Then
                MessageBox.Show("Text found, position is Row-" & oRng.Row & " and column-" & oRng.Column)
            Else
                MessageBox.Show("Text is not found")
            End If
            oWB.Close()
 
            oSheet = Nothing
            oWB = Nothing
            oXL.Quit()
 
        Catch ex As Exception
            If oSheet IsNot Nothing Then
                oSheet = Nothing
            End If
            If oWB IsNot Nothing Then
                oWB = Nothing
            End If
            If oXL IsNot Nothing Then
                oXL.Quit()
            End If
        End Try
    End Sub

We are using Find() method that Finds specific text in a range and returns a Range object that represents the first cell where that text is found. Below function returns Nothing if no match is found and this method doesn’t affect the selection or the active cell. One more thing you can also use FindNext and FindPrevious methods to repeat the search.

    Private Function GetSpecifiedRange(ByVal matchStr As String, ByVal objWs As Excel.Worksheet) As Excel.Range
        Dim currentFind As Excel.Range = Nothing
        Dim firstFind As Excel.Range = Nothing
        currentFind = objWs.Range("A1:AM100").Find(matchStr, , _
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, _
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, False)
        Return currentFind
    End Function