none
VB.net Excel Find Value and Cell RRS feed

  • Question

  • Hi, could someone direct me to a source that would demonstrate (a) how to find the first instance of a value in an Excel cell, and (b) return the cell reference of that instance, using VB.net code?

    For example:  The value "ABC" is first encountered (searching by rows) in cell K4139.

    Thanks.


    Michael Downing

    Thursday, March 2, 2017 3:45 PM

Answers

  • Here is an example

    Calling

    Dim fileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SomeFile.xlsx")
    
    Dim TextToFind As String = "Bill" ' exists
    Dim Value As String = SearchExcelWorkSheet(fileName, "Sheet1", "A", TextToFind)
    If Not String.IsNullOrWhiteSpace(Value) Then
        MessageBox.Show("Password is " & Value)
    Else
        MessageBox.Show("User not found")
    End If

    Function

        Private Function SearchExcelWorkSheet(
            ByVal FileName As String,
            ByVal SheetName As String,
            ByVal Column As String,
            ByVal SearchItem As String) As String
    
            Dim FoundValue As String = ""
    
            Dim FoundRow As Int32 = -1
    
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlTargetRange As Excel.Range = Nothing
            Dim Result As Excel.Range = Nothing
    
            xlApp = New Excel.Application
            xlApp.Visible = False
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)
    
            Dim Proceed As Boolean = False
    
            xlWorkSheets = xlWorkBook.Sheets
    
            ' Get the WorkSheet to search on
            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                If xlWorkSheet.Name = SheetName Then
                    Proceed = True
                    Exit For
                End If
    
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
    
            Next
    
            ' WorkSheet was located so search for text.
            If Proceed Then
                xlTargetRange = xlWorkSheet.Range(Column & "1")
    
                Result = xlTargetRange.Find(
                    SearchItem,
                    ,
                    Excel.XlFindLookIn.xlValues,
                    Excel.XlLookAt.xlWhole,
                    Excel.XlSearchOrder.xlByRows,
                    Excel.XlSearchDirection.xlNext,
                    False
                )
    
                If Result IsNot Nothing Then
                    FoundRow = Result.Row
                    Dim xlCells As Excel.Range = Nothing
                    xlCells = xlWorkSheet.Range("B" & FoundRow.ToString)
                    FoundValue = CStr(xlCells.Value)
                    If Not xlCells Is Nothing Then
                        Marshal.FinalReleaseComObject(xlCells)
                        xlCells = Nothing
                    End If
                End If
            Else
                ' --- Sheet not located in Excel file
                MessageBox.Show(String.Format("{0} was not located in {1}", SheetName, FileName))
            End If
    
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
            If Not Result Is Nothing Then
                Marshal.FinalReleaseComObject(Result)
                Result = Nothing
            End If
    
            If Not xlTargetRange Is Nothing Then
                Marshal.FinalReleaseComObject(xlTargetRange)
                xlTargetRange = Nothing
            End If
    
            If Not xlWorkSheets Is Nothing Then
                Marshal.FinalReleaseComObject(xlWorkSheets)
                xlWorkSheets = Nothing
            End If
    
            If Not xlWorkSheet Is Nothing Then
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
            End If
    
            If Not xlWorkBook Is Nothing Then
                Marshal.FinalReleaseComObject(xlWorkBook)
                xlWorkBook = Nothing
            End If
    
            If Not xlWorkBooks Is Nothing Then
                Marshal.FinalReleaseComObject(xlWorkBooks)
                xlWorkBooks = Nothing
            End If
    
            If Not xlApp Is Nothing Then
                Marshal.FinalReleaseComObject(xlApp)
                xlApp = Nothing
            End If
    
            Return FoundValue
    
    
        End Function
    
    

    Sheet data. We searched for Bill in column A, get Jones in Column B. FoundRow contains the row number

    xlCells = xlWorkSheet.Range("B" & FoundRow.ToString)


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Oakwinds Wednesday, March 15, 2017 7:38 PM
    Thursday, March 2, 2017 11:44 PM
    Moderator

All replies

  • Here is an example

    Calling

    Dim fileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SomeFile.xlsx")
    
    Dim TextToFind As String = "Bill" ' exists
    Dim Value As String = SearchExcelWorkSheet(fileName, "Sheet1", "A", TextToFind)
    If Not String.IsNullOrWhiteSpace(Value) Then
        MessageBox.Show("Password is " & Value)
    Else
        MessageBox.Show("User not found")
    End If

    Function

        Private Function SearchExcelWorkSheet(
            ByVal FileName As String,
            ByVal SheetName As String,
            ByVal Column As String,
            ByVal SearchItem As String) As String
    
            Dim FoundValue As String = ""
    
            Dim FoundRow As Int32 = -1
    
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlTargetRange As Excel.Range = Nothing
            Dim Result As Excel.Range = Nothing
    
            xlApp = New Excel.Application
            xlApp.Visible = False
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)
    
            Dim Proceed As Boolean = False
    
            xlWorkSheets = xlWorkBook.Sheets
    
            ' Get the WorkSheet to search on
            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                If xlWorkSheet.Name = SheetName Then
                    Proceed = True
                    Exit For
                End If
    
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
    
            Next
    
            ' WorkSheet was located so search for text.
            If Proceed Then
                xlTargetRange = xlWorkSheet.Range(Column & "1")
    
                Result = xlTargetRange.Find(
                    SearchItem,
                    ,
                    Excel.XlFindLookIn.xlValues,
                    Excel.XlLookAt.xlWhole,
                    Excel.XlSearchOrder.xlByRows,
                    Excel.XlSearchDirection.xlNext,
                    False
                )
    
                If Result IsNot Nothing Then
                    FoundRow = Result.Row
                    Dim xlCells As Excel.Range = Nothing
                    xlCells = xlWorkSheet.Range("B" & FoundRow.ToString)
                    FoundValue = CStr(xlCells.Value)
                    If Not xlCells Is Nothing Then
                        Marshal.FinalReleaseComObject(xlCells)
                        xlCells = Nothing
                    End If
                End If
            Else
                ' --- Sheet not located in Excel file
                MessageBox.Show(String.Format("{0} was not located in {1}", SheetName, FileName))
            End If
    
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
            If Not Result Is Nothing Then
                Marshal.FinalReleaseComObject(Result)
                Result = Nothing
            End If
    
            If Not xlTargetRange Is Nothing Then
                Marshal.FinalReleaseComObject(xlTargetRange)
                xlTargetRange = Nothing
            End If
    
            If Not xlWorkSheets Is Nothing Then
                Marshal.FinalReleaseComObject(xlWorkSheets)
                xlWorkSheets = Nothing
            End If
    
            If Not xlWorkSheet Is Nothing Then
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
            End If
    
            If Not xlWorkBook Is Nothing Then
                Marshal.FinalReleaseComObject(xlWorkBook)
                xlWorkBook = Nothing
            End If
    
            If Not xlWorkBooks Is Nothing Then
                Marshal.FinalReleaseComObject(xlWorkBooks)
                xlWorkBooks = Nothing
            End If
    
            If Not xlApp Is Nothing Then
                Marshal.FinalReleaseComObject(xlApp)
                xlApp = Nothing
            End If
    
            Return FoundValue
    
    
        End Function
    
    

    Sheet data. We searched for Bill in column A, get Jones in Column B. FoundRow contains the row number

    xlCells = xlWorkSheet.Range("B" & FoundRow.ToString)


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Oakwinds Wednesday, March 15, 2017 7:38 PM
    Thursday, March 2, 2017 11:44 PM
    Moderator
  • Hi Karen - sorry for the delay in response.

    Your information provided me the solution.  Here is a simple version using my example (I dd not need all the features you included):

     Dim Application01 As New Microsoft.Office.Interop.Excel.Application
     Dim Workbook01 As Microsoft.Office.Interop.Excel.Workbook = Application01.Workbooks.Open("C:Test.xlsx")
     Dim Worksheet01_01 As Microsoft.Office.Interop.Excel.Worksheet = CType(Workbook01.Worksheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet)
     Dim Range01_01 As Microsoft.Office.Interop.Excel.Range
     Dim Range01_02 As Microsoft.Office.Interop.Excel.Range
    
    Dim Row01_02 As Integer
    Dim Column01_02 As Integer
    
    Range01_01 = Worksheet01_01.UsedRange
    
    Range01_02 = Range01_01.Find("ABC")
    
    Row01_02 = Range01_02.Row
    Column01_02 = Range01_02.Column

    Thanks.  This concludes the thread.


    Michael Downing

    Wednesday, March 15, 2017 7:38 PM