This is what I have done so far, I used two buttons, it seems to work, the only funny thing is that when I click the Submit1 button, it asks if I want to replace the text.xlsx file
Imports Microsoft.Office.Interop
Public Class Form1
Dim xlApp As Excel.Application
Dim xlApp1 As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWB1 As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlsheet1 As Excel.Worksheet
Dim xlCell As Excel.Range
Dim xlCell1 As Excel.Range
Dim oExcel As Object
Dim oExcel1 As Object
Dim oBook As Object
Dim oBook1 As Object
Dim oSheet As Object
Dim oSheet1 As Object
Dim col As Integer
Dim lastRow As Excel.Range
Dim col1 As Integer
Dim lastRow1 As Excel.Range
Dim myValues(4) As String
Dim myValues2(4) As String
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Open("C:\test.xlsx") ' Change this string to the exact address of the excel file
oExcel1 = CreateObject("Excel.Application")
oBook1 = oExcel1.Workbooks.Open("C:\TestExcel.xlsx") ' Change this string to the exact address of the excel file
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Submit.Click
oSheet = oBook.Worksheets(1)
oExcel.visible = True
col = 1
myValues(0) = FName.Text
myValues(1) = LName.Text
myValues(2) = GCNum.Text
myValues(3) = PinNum.Text
myValues(4) = AmtNum.Text
Try
lastRow = oSheet.cells(oSheet.rows.count, col).End(Excel.XlDirection.xlUp)
For i = 0 To 4
oSheet.cells(lastRow.Row + 1, col).value = myValues(i)
col += 1
Next
oBook.save()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub Form1_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
End Sub
Private Sub Submit2_Click(sender As Object, e As EventArgs) Handles Submit2.Click
col1 = 1
oSheet1 = oBook1.Worksheets(1)
oExcel1.visible = True
myValues2(0) = FName.Text
myValues2(1) = LName.Text
myValues2(2) = GCNum.Text
myValues2(3) = PinNum.Text
myValues2(4) = AmtNum.Text
Try
lastRow1 = oSheet1.cells(oSheet1.rows.count, col1).end(Excel.XlDirection.xlUp)
For i = 0 To 4
oSheet1.cells(lastRow1.Row + 1, col1).value = myValues2(i)
col1 += 1
Next
oBook1.save()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles User_Exit.Click
oBook.close()
oBook1.close()
Me.Close()
End Sub
End Class