I use the following code to update an excel sheet:
This works great with excel 2003, but with excel 2000 it craps out. Basically what happens is the excel.exe process starts, but with it a process called AGENTSVC.exe starts as well and the whole thing just hangs. Does anybody know what is going on?
Thanx
VB.NET:
Dim MyCommand As OleDb.OleDbDataAdapter
Dim MyConnection As OleDb.OleDbConnection
Dim DSExcel As DataSet
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
If File.Exists("C:\temp.xls") Then
File.Delete("C:\temp.xls")
End If
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = xlApp.Workbooks.Add()
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
' Place some text in the second row of the sheet.
xlSheet.Cells(1, 1) = "a"
xlSheet.Cells(1, 2) = "b"
xlSheet.Cells(1, 3) = "c"
xlSheet.Cells(1, 4) = "d"
xlSheet.Cells(1, 5) = "e"
xlSheet.Cells(1, 6) = "f"
'xlSheet.Cells(1, 7) = "g"
xlSheet.Cells(1, 7) = "h"
xlBook.SaveAs("C:\temp.xls")
xlBook.Close()
xlApp.Quit()
Try
MyConnection = New OleDb.OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp.xls" & _
";Extended Properties=""Excel 8.0;HDR=yes;IMEX=0""")
MyConnection.Open()
MyCommand = New OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", MyConnection)
Dim cmdBuilder As New OleDbCommandBuilder(MyCommand)
cmdBuilder.QuotePrefix = "["
cmdBuilder.QuoteSuffix = "]"
'MyCommand.InsertCommand = cmdBuilder.GetInsertCommand()
DSExcel = New DataSet
MyCommand.FillSchema(DSExcel, SchemaType.Source, "excel")
MyCommand.Fill(DSExcel, "excel")
For i As Integer = 0 To lstMain.Items.Count - 1
Dim dr As DataRow = DSExcel.Tables(0).NewRow
dr(0) = lstMain.Items(i).Text
dr(1) = lstMain.Items(i).SubItems(1).Text
dr(2) = lstMain.Items(i).SubItems(2).Text
dr(3) = lstMain.Items(i).SubItems(3).Text
dr(4) = lstMain.Items(i).SubItems(4).Text
dr(5) = lstMain.Items(i).SubItems(5).Text
dr(6) = lstMain.Items(i).SubItems(6).Text
DSExcel.Tables(0).Rows.Add(dr)
dr = Nothing
Next
MyCommand.Update(DSExcel, "excel")
MyConnection.Close()
Catch ex As Exception
MsgBox(ex.Message)
MyConnection.Close()
Exit Sub
End Try
MsgBox("DONE")
System.Diagnostics.Process.Start("C:\temp.xls")
This works great with excel 2003, but with excel 2000 it craps out. Basically what happens is the excel.exe process starts, but with it a process called AGENTSVC.exe starts as well and the whole thing just hangs. Does anybody know what is going on?
Thanx