1

I have created a vb.net UserForm that feeds data into excel.But the problem is,anytime I enter new data the previous ones get replaced by the new data without any means of retrieval.So my argument is that;I want both the previous and newly entered data displayed on the worksheet.I really need the codes to solve this issue.And also code for displaying the same data i enter on my DataGridView.I have been trying for days and nights but still! Thanks in advance!

Private Sub BtnAddRecord_Click(sender As Object, e As EventArgs) Handles BtnAddRecord.Click
    Dim AppExcel As New Excel.Application 'Create a new Excel Application
    Dim workbook As Excel.Workbook = AppExcel.Workbooks.Add("C:\Users\DELL\OneDrive\Documents\VbNetFormDone.xlsx") ' 'Create a new workbook
    Dim sheet As Excel.Worksheet = workbook.Sheets("Sheet1") ' Create variable a Sheet, Sheet1 must be in WorkBook
    AppExcel.Visible = True
    AppExcel.DisplayAlerts = False
    'Work with range'

    Dim AddNew As Range
    AddNew = sheet.Range("A65356").End(XlDirection.xlUp).Offset(1, 0)


    'Range with text address
    AddNew.Offset(0, 0).Value = TextReferenceNo.Text
    AddNew.Offset(0, 1).Value = TextFirstname.Text
    AddNew.Offset(0, 2).Value = TextSurname.Text
    AddNew.Offset(0, 3).Value = TextAddress.Text
    AddNew.Offset(0, 4).Value = TextPostalCode.Text
    AddNew.Offset(0, 5).Value = TextTelephone.Text
    AddNew.Offset(0, 6).Value = TextDateOfReg.Text
    AddNew.Offset(0, 7).Value = TextProveOfId.Text
    AddNew.Offset(0, 8).Value = TextMemberType.Text
    AddNew.Offset(0, 9).Value = TextMemberFees.Text


    workbook.SaveAs("C:\Users\DELL\OneDrive\Documents\VbNetFormDone.xlsx", FileFormat:=56) 'Save the workbook
    workbook.Close() 'Close workbook
    AppExcel.Quit() 'Quit the application
Agbesi Innocent
  • 43
  • 8
  • 1
    I can't see where you went wrong here. However, what you are trying to do is probably more appropriate as a database. You can still export data from a database to Excel as needed. Switching your design to use a DB instead will open you up to a entirely new world of data processing. You'll find it a lot easier to do things like you're here. – Michael Z. Apr 27 '19 at 03:32
  • Part of development is being able to choose the right tools for the job. VB.net has libraries to connect you to many different databases. A good starting point could be Microsoft Access or SQL Sever local database files. – Michael Z. Apr 27 '19 at 03:37
  • Taking the chain of codes or the syntax into account,I have no issues with that.But the problem happens to be overwriting.Once I try entering a new data onto the worksheet,it overwrites the old saved ones.This is the only problem I want addressed.Thanks! – Agbesi Innocent Apr 27 '19 at 16:35
  • AddNew = sheet.Range("A65356").End(XlDirection.xlUp).Offset(1, 0) .......Hummm... It seems like you are finding the first empty row and writing data on that line. What's the problem, exactly? – ASH Jun 11 '19 at 04:20

1 Answers1

0

It appears your are creating a new Excel workbook every time. This is why your new data is over-writing your old data.

Try changing this line to open the workbook instead of creating a new one.

Dim workbook As Excel.Workbook = AppExcel.Workbooks.Open("C:\Users\DELL\OneDrive\Documents\VbNetFormDone.xlsx")

References:
Add()
https://learn.microsoft.com/en-us/office/vba/api/excel.workbooks.add

Open()
https://learn.microsoft.com/en-us/office/vba/api/Excel.Workbooks.Open

Michael Z.
  • 1,453
  • 1
  • 15
  • 21
  • Thank you so much but once I get the said line deleted,this is the Exception message I get 'System.NullReferenceException: 'Object reference not set to an instance of an object.' I will really appreciate the whole syntax of codes sent to me.Thanks! – Agbesi Innocent Apr 27 '19 at 16:29
  • Don't delete that line. Change it from `.Add()` to `.Open()` – Michael Z. Apr 27 '19 at 16:37
  • It's still not working.I believe it's this line of code( Dim appExcel As New Excel.Application) causing all this mess on my project.I accomplished same project in VBA 6 with so much ease – Agbesi Innocent Apr 28 '19 at 01:47
  • That just creates a new instance of Excel. If you're successfully writing anything to Excel then this part is working. I'm wondering now if your issue is coming from this line - `AddNew = sheet.Range("A65356").End(XlDirection.xlUp).Offset(1, 0)` - it's not a reliable way to get the last used row. Check out this SO question for better ways - https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba – Michael Z. Apr 28 '19 at 02:29