Hi Xanderno,
I found a way to work around this. It works well on my machine, but it crashes on the development server. I am not quite sure why because I just copied and pasted it. Incase you want to know the code:
Private
objExcel As New Excel.Application
Private wb As Excel.Workbook
Private strSQL As String
Private ds As New DataSet
Private dr As DataRow
Private strConn As String
Private eventDel_BeforeBookClose As Excel.AppEvents_WorkbookBeforeCloseEventHandler
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Dim file As FileInfo
Dim file2 As FileInfo
file =
New FileInfo("C:\Junk\coba.xls")
file2 =
New FileInfo("C:\Junk\coba2.xls")
' If the workbooks already exist, prompt to delete.
If Dir("C:\Junk\coba.xls") <> "" Or Dir("C:\Junk\coba2.xls") <> "" Then
If Dir("C:\Junk\coba.xls") <> "" Then file.Delete()
If Dir("C:\Junk\coba2.xls") <> "" Then file2.Delete()
End If
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Junk\coba.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES"""
Dim conn As New OleDbConnection
conn.ConnectionString = strConn
conn.Open()
Dim cmd1 As New OleDbCommand
cmd1.Connection = conn
cmd1.CommandText = "CREATE TABLE SalesPromo (UPC char(255), store_num char(255))"
cmd1.ExecuteNonQuery()
strSQL = "Select * from salesPromotion "
ds = DataHandler.GenDataSet(strSQL, DataHandler.GetConnStr())
For Each dr In ds.Tables(0).Rows
cmd1.CommandText = "INSERT INTO SalesPromo (UPC, store_num) values " & _
"('" & dr("UPC") & "', '" & dr("store_num") & "')"
cmd1.ExecuteNonQuery()
Next
conn.Close()
wb = objExcel.Workbooks.Open("C:\Junk\coba.xls")
Dim strWorkSheetName As String = "SalesPromo"
Dim sbExcelData As System.Text.StringBuilder
Dim aWorkSheet As Excel.Worksheet
aWorkSheet =
CType(wb.Sheets(strWorkSheetName), Excel.Worksheet)
aWorkSheet.Activate()
eventDel_BeforeBookClose =
New Excel.AppEvents_WorkbookBeforeCloseEventHandler(AddressOf BeforeBookclose)
AddHandler objExcel.WorkbookBeforeClose, eventDel_BeforeBookClose
objExcel.Visible = True
lblStatus.Text = "Please click the button "
End If
End Sub
Private Sub BeforeBookclose(ByVal wb As Excel.Workbook, ByRef cancel As Boolean)
wb.SaveAs("C:\Junk\coba2.xls")
'Close running excel app
objExcel.Quit()
'Use the Com Object interop marshall to release the excel object
ReleaseComObject(objExcel)
objExcel =
Nothing
'force a garbage collection
System.GC.Collect()
End Sub
Private Sub UpdateDB()
Try
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Junk\coba2.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES"""
Dim conn2 As New OleDbConnection(strConn)
Dim da As New OleDbDataAdapter("Select * From SalesPromo", conn2)
Dim ds As DataSet = New DataSet
da.Fill(ds)
Dim dr As DataRow
For Each dr In ds.Tables(0).Rows
lbl.Text = dr("UPC") & " " & dr("store_num")
Next
conn2.Close()
Catch ex As Exception
lbl.Text = ex.Message.ToString
Catch exDB As OleDbException
lbl.Text = exDB.Message.ToString
End Try
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
UpdateDB()
lblStatus.Text = "DB is updated."
End Sub