how to append data to existing excel file using vb.net?

Question

i have code which finds the last non empty row in an existing excel file. i want to insert data from 5 textbox to the next 5cells in column. i dont know how to code it. please help me, here is my code:

With xlWorkSheet
        If EXL.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("A2"), _
                          LookAt:=Excel.XlLookAt.xlPart, _
                          LookIn:=Excel.XlFindLookIn.xlFormulas, _
                          SearchOrder:=Excel.XlSearchOrder.xlByRows, _
                          SearchDirection:=Excel.XlSearchDirection.xlPrevious, _
                          MatchCase:=False).Row
        Else
            lRow = 1
        End If

    End With

Bugs · Accepted Answer · 2016-12-22 13:13:21Z

1

Here are two different solutions to your problem. I like the second better because you can determine a lastrow from any column and if you cared reference it to another column.

Dim nextrow As Integer = excel.Rows.End(XlDirection.xlDown).Row + 1

Dim ws As Worksheet = excel.ActiveSheet
Dim nRow = ws.Range("A" & ws.Rows.Count).End(XlDirection.xlUp).Row + 1

Then all you have to do to assign a value is:

excel.Range("A" & nRow).Value = "test"
excel.range("B" & nRow).value = "adjacent column"

If you want to loop it to 5 cells below that then do:

Dim ws As Worksheet = excel.ActiveSheet
Dim lRow = ws.Range("A" & ws.Rows.Count).End(XlDirection.xlUp).Row

For i = 1 To 5
    excel.Range("A" & lRow).Offset(i, 0).Value = "variable here"
Next

edited Dec 22, 2016 at 13:13

Bugs

4,4799 gold badges34 silver badges41 bronze badges

answered Mar 9, 2013 at 5:28

Jason Bayldon

1,2966 gold badges25 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to append data to existing excel file using vb.net?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related