469,623 Members | 1,928 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,623 developers. It's quick & easy.

Problem with using Excel.Application object

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
Nov 20 '05 #1
4 2287
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
.

Nov 20 '05 #2
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
.

Nov 20 '05 #3
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
>
>
>.
>

.

Nov 20 '05 #4
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
>
>
>.
>

.

Nov 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

14 posts views Thread by pmud | last post: by
reply views Thread by Adrian Belen via .NET 247 | last post: by
4 posts views Thread by goonsquad | last post: by
5 posts views Thread by Scott M. Lyon | last post: by
6 posts views Thread by Darrell Wesley | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.