VB.net Excel Find Value and Cell

2017-03-02

Question

_{Thursday, March 2, 2017 3:45 PM}

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

All replies (2)

_{Thursday, March 2, 2017 11:44 PM ✅Answered | 1 vote}

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

_{Wednesday, March 15, 2017 7:38 PM}

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

שתף באמצעות

VB.net Excel Find Value and Cell

Question

All replies (2)

משאבים נוספים