CODEWITHSUNDEEP
vb.netwinformsexcelwinforms-interop
user3363495
user3363495

Reputation: 155

Find specific data in excel sheet using vb.net

I have been trying to find a specific data in Excel through a WinForm application and then try to fetch the next value in the same row using vb.net.

Example:
ElementName Value
Age          24
Name    John Clan
Music   Rock

Suppose if I want to find Age. then I want to make the code return as 24 searching it from Excel file.

I tried pulling the whole Excel data into a Dataset but it wasn't helpful.

Kindly guide me.

Upvotes: 0

Views: 9874

Answers (1)

user3363495
user3363495

Reputation: 155

Imports Interop = Microsoft.Office.Interop.Excel

'Find In Excel
    Private Function FindValueInExcel(ByVal sTextToFind)
        Dim currentFind As Interop.Range = Nothing
        Dim firstFind As Interop.Range = Nothing
        Dim xlappFirstFile As Interop.Application = Nothing
        Dim sSearchedValue As String

        xlappFirstFile = CreateObject("Excel.Application")

        xlappFirstFile.Workbooks.Open("D:\Sample.xlsx")

        Dim rngSearchValue As Interop.Range = xlappFirstFile.Range("A1", "C5")

        currentFind = rngSearchValue.Find(sTextToFind, , _
            Interop.XlFindLookIn.xlValues, Interop.XlLookAt.xlPart, _
            Interop.XlSearchOrder.xlByRows, Interop.XlSearchDirection.xlNext, False)

        If Not currentFind Is Nothing Then
            sSearchedValue = DirectCast(DirectCast(currentFind.EntireRow, Microsoft.Office.Interop.Excel.Range).Value2, System.Object)(1, 3).ToString()
        Else
            sSearchedValue = ""
        End If

        Return sSearchedValue 

    End Function

Link which helped me - http://msdn.microsoft.com/en-us/library/e4x1k99a.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1

Upvotes: 3

Related Questions