Overwrite previously saved data

[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.

[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?

[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

.'

[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.

[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'

[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.

[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.

[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.