Can open Excel, but how do I save changes?

[DNAunion2000]

DNAunion: My simple form has 2 buttons (button1 = "Populate" and button2 = "Save") and 1 datagrid. Clicking "Populate" opens a connection to an Excel spreadsheet, populates an ADO.NET DataSet from it, and binds the data to the DataGrid control. I can move around in the data and make changes. That works fine. But how do I save the changes back to the worksheet???


Public Class Form1
Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "


Dim MyConnection As New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Documents and Settings\Rick\My Documents\Book1.XLS; " & _
"Extended Properties=Excel 8.0;")
Dim MyDataAdapter As New System.Data.OleDb.OleDbDataAdapter
Dim MySelectCommand As New System.Data.OleDb.OleDbCommand("select * from [Sheet1$]")
Dim MyUpdateCommand As New System.Data.OleDb.OleDbCommand("update [Sheet1$]")
Dim MyDataSet As New System.Data.DataSet


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
MySelectCommand.Connection = MyConnection
MyDataAdapter.SelectCommand = MySelectCommand

MyDataAdapter.Fill(MyDataSet, "ExcelImport")
DataGrid1.DataSource = MyDataSet.Tables(0)
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
MyUpdateCommand.Connection = MyConnection
MyDataAdapter.UpdateCommand = MyUpdateCommand

Try
MyDataAdapter.Update(MyDataSet, "ExcelImport")
Catch Except As Exception
MsgBox(Except.Message)
Finally
End Try

End Sub
End Class


PS: I know it is considered bad style by many to use module-level connections, dataadapters, etc. as opposed to JIT ones (i.e., recreating them on the fly, as needed). I'm not that interested in style right now...I want to know how to get it to work.

[imissu]

i remember that i used to do something with Excel, i'' tell you later. Let me have a check

[dgjoni]

I had the same problem myself, but the only way to get rid of the stupid box, is to first try this "neat way" microsoft suggets:

Application.DisplayAlerts = False

but if this doesnt work (for me it didnt)

just kill the file and save it later

Kill ("f:\paeon\myfile.xls")
objBook.SaveAs ("f:\paeon\myfile.xls")

hope this helps
d