Help with writing rows to Excel please

[Santor]

First, I am a VB/ADO newbie so type S L O W L Y when answering please.
I have collected and modified some code that watches folders and when a file arrives in a watched folder it writes the file name and date to a row in an Excel database using Insert into.
It works OK until I open the DB and delete rows. The next time the program executes (new file arrives in folder) the new row in the DB is added where it would have been if I had not deleted the old rows.
For example, I delete rows 1-7 from the DB, save and close it, a file is added to a watched folder, when I open the DB the new data is on row 8 with rows 1-7 empty.
How do I make it use the first empty row or reset whatever is keeping a count on used rows?

 

[jebenson]

Could you post the code where the data are written to the spreadsheet?



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[Santor]

As requested here is my code...

Private Sub Add_Record(ByVal source As Object, ByVal e As _ System.IO.FileSystemEventArgs)
Dim en As New CultureInfo("en-US")
Thread.CurrentThread.CurrentCulture = en
Dim uFile As String = e.Name
Dim TheDate As String = DateTime.Now
Dim Fle As String = "C:\FileNotify.xls"
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Fle _
& ";" & "Extended Properties=Excel 8.0;"
Dim Conn1 As New System.Data.OleDb.OleDbConnection(sConnectionString)
Conn1.Open()
Dim cmd As New System.Data.OleDb.OleDbCommand
cmd.Connection = Conn1
cmd.CommandText = "INSERT INTO MYDATA (File, Pending) VALUES(@au_file,@au_date)"
cmd.Parameters.Add("@au_file", uFile)
cmd.Parameters.Add("@au_date", TheDate)
cmd.ExecuteNonQuery()
Conn1.Close()
End Sub

 

[jebenson]

Well, nothing there that's obvious. Try this...read the existing data from the Excel file into a datatable, add your new records to this datatable and then save the changes back to the Excel file. Hopefully this should prevent the skipped lines.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira