Appending to Excel file; last row does not increment

[TheBradish]

I have a form with 4 text boxes and a check box used to input data. At a button click, the application opens an Excel file and writes one row of data into the spreadsheet from the form. Problem is, the application keeps writing over the same row instead of adding it to the end. (This is my first time doing file I/O, and I'm not very good with Excel to begin with.)

Originally, the first cell in every row was empty, except for a label near the top; no data was being written to the first cell. So when my application looked for the last row in the spreadsheet, it always found the same row (2) and wrote to the same row (3).

Once I caught on to what was happening, I put in a line of code to put a dummy value into the first cell. After that, the application would successfully increment to the next row instead of writing over the same row.

THEN I tried to clean up the spreadsheet by DELETING the entire first column. No more dummy-values. The app will write useful date to the file beginning with the first cell of the last row. Except now it doesn't seem to be recognizing the written data in the first cell, and my app is once again over-writing the same row. Anybody have some idea of what I am missing?

Private Sub ButtonEnter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonEnter.Click
        Call Text_Validate(False)
        'Cancel sub if validation fails
        If IsValid = False Then
            Exit Sub
        End If
        'Check if log exists for today.  Creates log if none exists.
        If My.Computer.FileSystem.FileExists("C:\Documents and Settings\user\Desktop\PartsList_" & DateString & ".xlsx") Then
            MsgBox("File Found")
        Else
            MsgBox("File not found.  New file created.")
            My.Computer.FileSystem.CopyFile("C:\Documents and Settings\user\Desktop\PartsListMaster.xlsx", "C:\Documents and Settings\MAROIS\Desktop\PartsList_" & DateString & ".xlsx")
        End If
        Call Append()
    End Sub

Private Sub Append()
        Dim xls As New Excel.Application
        Dim book As Excel.Workbook
        Dim sheet As Excel.Worksheet


        xls.Workbooks.Open("C:\Documents and Settings\user\Desktop\PartsList_" & DateString & ".xlsx")
        'get references to first workbook and worksheet
        book = xls.ActiveWorkbook
        sheet = book.ActiveSheet
        
        'append
        Dim nextRow As Integer = sheet.UsedRange.End(Excel.XlDirection.xlDown).Row + 1
        MsgBox(sheet.UsedRange.End(Excel.XlDirection.xlDown).Row)
        MsgBox(nextRow)
        sheet.Cells(nextRow, 1) = TextBoxPN.Text
        sheet.Cells(nextRow, 2) = TextBoxOp.Text
        sheet.Cells(nextRow, 3) = TextBoxOperator.Text
        sheet.Cells(nextRow, 4) = TextBoxQty.Text
        If CheckBox_FirstPc.Checked = True Then
            sheet.Cells(nextRow, 5) = "Yes"
        End If

        'save the workbook and clean up
        book.Save()
        xls.Workbooks.Close()
        xls.Quit()
        releaseObject(sheet)
        releaseObject(book)
        releaseObject(xls)
    End Sub

[jayinthe813]

Why don't you try using this to get the next row:

Dim NextRow = sheet.Range("A65536").End(Excel.XlDirection.xlUp).Row + 1

Replace "A" with your column name that you expect to use to find the next row. IMHO its cleaner than using Worksheet.UsedRange because you are explicitly stating which column to look at in order to find the next row.

The other things I would look at:

Instead of using

Sheet.Cells(X,Y)

, use

 sheet.range("A" & nextrow).Value = "XYZ"

I say this because it sucks tracking cells by numberlocation (Again, IMO). You can always do

sheet.range("A1").Offset(X,Y).Value

to get offset values.