Finding Last Blank Cell Of The Excel (In Column Range H8:H38), and Updating Blank Cell In Desired Range - VB.NET

Question

Sources:

Private Sub toEditDataOnReportCell(ByVal passedData As String)
    Dim monthlyReportFilePath As String = "./Report/Example.xlsx"

    Dim oExcel As Object
    Dim oBook As Object
    Dim oSheet As Object

    'Start a new workbook in Excel
    oExcel = CreateObject("Excel.Application")
    oBook = oExcel.Workbooks.Open(monthlyReportFilePath)

    'Set which worksheet tobe modified
    oSheet = oBook.Worksheets(1)

    'This will find the lastRow in the sheet
    'Dim lastRow As Integer = 7
    Dim ETLastRow As Integer = oSheet.usedrange.rows.count
    'This is next emptyRow in the sheet
    Dim ETemptyRow As Integer = ETLastRow + 1


    'edit last empty row val on column 8
    Dim columnToMod As Integer = 8
    oSheet.Cells(ETemptyRow, columnToMod).value = passedData


    ETLastRow = oSheet.usedrange.rows.count
    ETemptyRow = ETLastRow + 1


    'This will not prompt the user to overwrite the excel sheet
    oExcel.DisplayAlerts = False
    oBook.Save()
    oBook.Close()
    oExcel.Quit()

    TextBox12.AppendText("Sucess!!!" + vbCrLf + vbCrLf)

End Sub

• Giving wrong last row, if in excel doc already have a table with formula in it.

Output Preview:

• Targeting In Cell Range (H8:H38) [1]: https://i.stack.imgur.com/D4RT5.png

• But Output In Cell Range (H40:H) [2]: https://i.stack.imgur.com/0wufs.png

Answer 1

Hehehe, i found the solutions, Thanks to @StoriKnow & his answer there:

Here is my latest sources:

Private Sub toEditDataOnReportCell(ByVal passedData As String)
    Dim monthlyReportFilePath As String = "./Report/Example.xlsx"

    Dim oExcel As New Object
    Dim oBook As New Object
    Dim oSheet As New Object

    oExcel = CreateObject("Excel.Application")
    oBook = oExcel.Workbooks.Open(monthlyReportFilePath)
    oSheet = oBook.Worksheets(1)

    Try
        Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
        Dim currentRowValue As String

        sourceCol = 8   'column H has a value of 8
        rowCount = oSheet.Cells(oSheet.Rows.Count, sourceCol).End(Excel.XlDirection.xlUp).Row


        'for every row, find the first blank cell and select it
        For currentRow = 8 To rowCount
            currentRowValue = oSheet.Cells(currentRow, sourceCol).Value
            If (currentRowValue Is Nothing) Or (currentRowValue = "") Then
                'oSheet.Cells(currentRow, sourceCol).Select

                Dim preview As String = currentRow
                TextBox12.AppendText(preview + "  ")

                oSheet.Cells(currentRow, sourceCol).Select
                oSheet.Cells(currentRow, sourceCol).value = passedData

                Exit Try
            End If
        Next
    Finally
        ''' DO NOTHING
    End Try
    ''This will not prompt the user to overwrite the excel sheet
    oExcel.DisplayAlerts = False
    oBook.Save()
    oBook.Close()
    oExcel.Quit()
end Sub

Output that achived: