VB.NET get specific cell value from excel-CodePudding

I am trying to evaluate if specific cell value in an excel table is "" to use in an if statement in my VB.NET application. I modified the code that I use for writing to excel, but it doesn't work to get the cell value. The code I have:

   Sub Excel_LoadProjectsSchedule()

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open("G:\100 Databases\Projects Schedule.xlsx")
        xlApp.Visible = False
        xlWorkSheet = xlWorkBook.Worksheets("sheet1")
        Dim ProjectFinished  as boolean

        'Set variables
        Result = xlWorkSheet.Cells.Find(ProjectNumber, LookAt:=Excel.XlLookAt.xlWhole)

        If xlWorkSheet.Cells(Result.Row, 3).value = "" Then
            ProjectFinished = False
        Else
            ProjectFinished = True
        End If

        'Save and close
        xlApp.DisplayAlerts = False
        xlWorkBook.Close(SaveChanges:=True)
        xlApp.Quit()

    End Sub

Error is on

If xlWorkSheet.Cells(Result.Row, 3).value = "" Then

And it says "System.MissingMemberException: 'Public member 'value' on type 'Range' not found.' "

I do have

Public xlApp As Excel.Application = Nothing
Public xlWorkBook As Excel.Workbook = Nothing
Public xlWorkSheet As Excel.Worksheet = Nothing

Outside the sub in this module.

What am I doing wrong, could someone, please, help me solve this one?

CodePudding user response：

I think if you specifically want to check contents in the 3rd Column of the Row with that Projectnumber you're not far away from the Solution. I only tested it inside of VBA but something along the Lines of:

Sub Excel_LoadProjectsSchedule()
    Dim xlWorksheet As Worksheet, Result As Range, ProjectFinished  As Boolean
    xlApp = New Excel.Application
    xlWorkBook = xlApp.Workbooks.Open("G:\100 Databases\Projects Schedule.xlsx")
    xlApp.Visible = False
    Set xlWorkSheet = xlWorkBook.Worksheets("sheet1")
    
    'Set variables
     Set Result = xlWorksheet.Cells.Find(Projectnumber, LookIn:=Excel.XlLookin.xlValues, LookAt:=Excel.XlLookAt.xlWhole)
     if not Result is nothing then
     If Cells(Result.Row, 3).Value = "" Then
         ProjectFinished = False
     Else
         ProjectFinished = True
     End If
 End Sub

The Problem being, that "Result" hasn't been asigned to a Range, so your code coudn't access the Row Property.

CodePudding user response：

If looking for a certain cell in certain row .

 Dim AppExcel As Object
    Dim workBook As Object
    AppExcel = CreateObject("Excel.Application")

    workBook = AppExcel.Workbooks.Open("C:\SO\SO.xlsx")
    AppExcel.Visible = False

    Worksheets = workBook.worksheets
    If Worksheets("Blad1").Cells(2, 3).text = "" Then

        MessageBox.Show("Empty")

    Else
        MessageBox.Show("Text")
    End If

Then the close part

CodePudding user response：

I did some casting so the compiler can recognize the types.

Sub Excel_LoadProjectsSchedule(ProjectNumber As Integer)

    Dim xlApp = New Excel.Application
    Dim xlWorkBook = xlApp.Workbooks.Open("G:\100 Databases\Projects Schedule.xlsx")
    xlApp.Visible = False
    Dim xlWorkSheet = DirectCast(xlWorkBook.Worksheets("sheet1"), Excel.Worksheet)
    Dim ProjectFinished As Boolean

    'Set variables
    Dim Result = xlWorkSheet.Cells.Find(ProjectNumber, LookAt:=Excel.XlLookAt.xlWhole)
    Dim row = Result.Row
    Dim cell = DirectCast(xlWorkSheet.Cells(row, 3), Excel.Range)
    If cell.Value.ToString = "" Then
        ProjectFinished = False
    Else
        ProjectFinished = True
    End If

    'Save and close
    xlApp.DisplayAlerts = False
    xlWorkBook.Close(SaveChanges:=True)
    xlApp.Quit()

End Sub