Problem with using Excel.Application object

  • Thread starter Thread starter msnnews.msn.com
  • Start date Start date
M

msnnews.msn.com

hi there,

i've got a form that populates a datagrid, and a button that calls a
function to export to an excel file.
All is well with the export, no errors are returned, but the Excel instance
doesnt want to terminate properly even when i set excelApp.Quit, and
excelApp = nothing.

has anyone else experienced a problem like this???
code below:
--------------------------------------------------------
Sub SaveToExcel(ByVal table As String, ByVal sql As String)

Try
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlQryTable As Excel.QueryTable
xlBook = xlApp.Workbooks.Add()
xlApp.DisplayAlerts = False
Do Until xlBook.Sheets.Count = 1
xlBook.Sheets(1).Delete()
Loop

xlSheet = xlBook.ActiveSheet
xlSheet.Name = "QueryBuilder Data"
xlQryTable = xlSheet.QueryTables.Add("OLEDB;" & strConn,
xlSheet.Range("A1"))
With xlQryTable
.Sql = sql
.Refresh()
End With

xlApp.DisplayAlerts = True
Dim strBookName As String = xlApp.GetSaveAsFilename(table,
fileFilter:="Excel Files (*.xls), *.xls")
If strBookName <> "False" Then xlBook.SaveAs(strBookName)
xlSheet = Nothing
xlQryTable = Nothing
xlBook.Close(False)
xlBook = Nothing
xlApp.Quit()
xlApp = Nothing
Application.DoEvents()
Catch ex As Exception
MessageBox.Show("An error occured while exporting to an Excel file.")
End Try

End Sub
 
Hello Paul
I use a similar function and it quits normally.
Try the following :
System.Runtime.InteropServices.Marshal.ReleaseComObject
(objExcelApp)

Kind Regards
Jorge
 
Jorge,

i added the extra line you mentioned, but still doesnt unload it.
After line by line elimination, i have found the problem to be when i load
the following:

xlBook = xlApp.Workbooks.Add()

although i unload this object before i unload the Excel object, it still
doesnt solve my problem..
Also, i added the ReleaseComObject on this object , but still no luck.

Any ideas?
thanks,
Paul.
 
Paul,
This is how i declare them:
Dim objExcelApp As New Excel.Application
Dim objBook As Excel.Workbook = objExcelApp.Workbooks.Add
Dim objSheet As Excel.Worksheet = DirectCast
(objExcelApp.ActiveSheet, Excel.Worksheet)

This is how i terminate them:
objBook.Close()
objSheet = Nothing
objExcelApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject
(objExcelApp)
objExcelApp = Nothing
GC.Collect()

Kind Regards
Jorge
 
thanks jorge,

i used your example and it works fine - although i have no idea what was the
problem with my example:))

Paul.
 
Back
Top