Member Avatar for Gus_19

I'm trying to figure out/learn how to overwrite previously saved data. Right now when I save data to my excel form and then reopen it, to edit, a duplicate row with the edited data is shown along with the previously saved data as well.

  • 3 Contributors
  • 7 Replies
  • 217 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Gus_19

Dani AI

Generated

Short version: the duplicates come from writing the in-memory rows back to Excel without matching or removing the original rows. was right about “you are just adding everything back in again.” Two practical fixes: either locate and overwrite the matching row (recommended), or clear the sheet first and rewrite everything. Also check the snippet that clears "Event Info" then merges PERSON_EXCEL_DATA — that looks like a copy/paste bug; you probably meant to merge the event table.

If you want to update rows in your DataTable (so saves update instead of append), give the table a real primary key and detect matches before inserting. Example pattern in VB.NET:

' set the primary key once (use the unique ID column)
Dim pk(0) As DataColumn
pk(0) = personTable.Columns("PersonID")
personTable.PrimaryKey = pk

' for each row read from the Excel source, look for an existing row and update or import
For Each src As DataRow In excelSource.Rows
    Dim found As DataRow = personTable.Rows.Find(src("PersonID"))
    If found IsNot Nothing Then
        For Each c As DataColumn In personTable.Columns
            found(c.ColumnName) = src(c.ColumnName)
        Next
    Else
        personTable.ImportRow(src)
    End If
Next

If you prefer ’s Excel Interop approach, search the sheet for the unique key and write values directly to the matching row — that avoids rewriting the whole sheet. If you use Interop, be sure to save, close and release COM objects to avoid orphaned Excel processes:

workbook.Save()
workbook.Close()
xls.Quit()
Runtime.InteropServices.Marshal.ReleaseComObject(worksheet)
Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
Runtime.InteropServices.Marshal.ReleaseComObject(xls)
worksheet = Nothing : workbook = Nothing : xls = Nothing
GC.Collect() : GC.WaitForPendingFinalizers()

Quick checklist: use a unique ID column, make sure column names/trim/spacing match, test on a copy of the file, decide whether you want in-place updates or full rewrite, and fix the merge-target bug in the Event Info code. These steps will stop duplicate rows and make saves predictable.

Recommended Answers

All 7 Replies

Member Avatar for Reverend Jim

How are you opening/modifying/saving the data? Please post your code so we can tell you where you are going astray. Are the duplicate rows being inserted or are they being appended to the worksheet?

Member Avatar for Gus_19

The duplicate rows are being inserted and not being appended. One of the tabs used, I was able to clearform() since it is only one row of data, but the other tabs have multiple rows from the excel sheet, which I scroll through with buttons so I am unable to just clear the form.

If PERSON_EXCEL_DATA.Rows.Count > 0 Then
                    PFC_Xpress.Tables("PERSON").Merge(PERSON_EXCEL_DATA)
                    For Each CURRENTROW As DataRow In PERSON_EXCEL_DATA.Rows
                        Me.EVENT_ASSOC1.Text = PFC_Xpress.Tables("PERSON").Rows(0).Item("Event Association").ToString
                        Me.Name1.Text = PFC_Xpress.Tables("PERSON").Rows(0).Item("NAME").ToString



'Here is what I have for the tab which I am able to just 'clear

 If EVENT_INFO_EXCEL_DATA.Rows.Count > 0 Then
                    PFC_Xpress.Tables("Event Info").Clear()
                    PFC_Xpress.Tables("Event Info").Merge(PERSON_EXCEL_DATA)
                    For Each CURRENTROW As DataRow In EVENT_INFO_EXCEL_DATA.Rows
                        Me.EVENT_NUMBER.Text = CURRENTROW.Item("EVENT NUMBER").ToString

.'

Member Avatar for Reverend Jim

Please reread my original response. Taken out of context I have no idea what PERSON_EXCEL_DATA and other objects are. Some more details on what you are trying to do would help.

Member Avatar for Gus_19

I am reopening a saved excel spreadsheet datatable back into my windows form. After doing so, I will edit or add more data in the windows form, save it (under same filename), but when I open the actual excel spreadsheet there are duplicate entries. For example, the first row is the information from when I saved the excel file the first time and the second row is all of the information from the first time entering data plus the updated information I entered the second time. I am trying to figure out how I can overwrite/delete the first row of old information so there is no duplicate. I used 'clearform' for part of the datatable in my program because there is only one row of data there, but the problem I am running into is the other parts where there is more than one row in the datatable and since my windows form only displays one row at a time, I can't just use 'clearform'

Member Avatar for Begginnerdev

Are you appending the data that is in memory back out the file? If so you are just adding everything back in again.

Example:

If you are reading into table > add more to end > post back out = Post all data in table, including data that is already in the workbook.

You can compare line by line to the table to see if the data is already in the workbook, if so, don't add it and visa versa.

Member Avatar for Reverend Jim

The easiest way to modify Excel from VB is probably by using the Excel Application object. To do this,

1) start a new project
2) add a reference to "Microsoft Excel ##.# Object Library"
3) add "Imports Excel = Microsoft.Office.Interop.Excel" at the top of your code

You create the Excel object by

Dim xls As New Excel.Application

Open an existing workbook by

xls.Workbooks.Open("d:\my documents\points-jim.xls")

Create a reference to a particular sheet (for convenience) by

Dim sheet As Excel.Worksheet
sheet = xls.Workbooks(1).Worksheets(1)

You can access individual cells as

Dim row As Integer = 1
Dim col As Integer = 1

MsgBox(sheet.Cells(row, col).value)

I've attached a sample project which exports the data in a ListView control to an Excel spreadsheet. That should get you started.

2012-02-28_09-15_ExcelExportListView.zip (208.47 KB)
Member Avatar for Gus_19

Thank you for the post guys. The example worked great Jim. I'm new to all of this and I know I should have started smaller, but figured there's no quicker way to learn than throwing myself into the mix of it all.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.