Problem with using Excel.Application object

[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

--------------------------------------------------------
thanks,
Paul

[Jorge]

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

Kind Regards
Jorge

-----Original Message-----
hi there,

i've got a form that populates a datagrid, and a button that calls afunction to export to an excel file.
All is well with the export, no errors are returned, but the Excel instancedoesnt want to terminate properly even when i set excelApp.Quit, andexcelApp = 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

--------------------------------------------------------
thanks,
Paul
.

[Paul M]

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.

"Jorge" <an*******@discussions.microsoft.com> wrote in message
news:13*****************************@phx.gbl...

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

Kind Regards
Jorge

-----Original Message-----
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

--------------------------------------------------------
thanks,
Paul
.

[Jorge]

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.ReleaseComO bject
(objExcelApp)
objExcelApp = Nothing
GC.Collect()

Kind Regards
Jorge

-----Original Message-----
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 loadthe following:

xlBook = xlApp.Workbooks.Add()

although i unload this object before i unload the Excel object, it stilldoesnt solve my problem..
Also, i added the ReleaseComObject on this object , but still no luck.
Any ideas?
thanks,
Paul.

"Jorge" <an*******@discussions.microsoft.com> wrote in messagenews:13*****************************@phx.gbl...

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

Kind Regards
Jorge

>-----Original Message-----
>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
>
>------------------------------------------------------

-- >thanks,
>Paul
>
>
>.
>

.

[Paul M]

thanks jorge,

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

Paul.

"Jorge" <an*******@discussions.microsoft.com> wrote in message
news:13*****************************@phx.gbl...

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.ReleaseComO bject
(objExcelApp)
objExcelApp = Nothing
GC.Collect()

Kind Regards
Jorge

-----Original Message-----
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.

"Jorge" <an*******@discussions.microsoft.com> wrote in

message

news:13*****************************@phx.gbl...

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

Kind Regards
Jorge
>-----Original Message-----
>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
>
>------------------------------------------------------ -- >thanks,
>Paul
>
>
>.
>

.