Excell won't close

[joel009]

We use VB6/SP4 to export to Excell and the export works fine. The problem is using the quit or close method leaves Excell running if you check in Task Manager. If we exit the VB application the Excell instance then closes. We would like to close Excell as our cst runs multiple reports and each one leave an instance of Excell open and uses up the client resources.

Here is how we close:

xlApp.ActiveWorkbook.Close
xlApp.Quit

Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing

 

[Custom24]

I've had a similar problem before and from this forum I found out that if you instantiate using something like

Dim xlApp as Excel.Application
Set xlApp = new Excel.Application

then you get the problems you are experiencing (must be a bug - a circular reference or something)

However, if you use a function like this (which also takes advantage of the fact that there may be an instance of excel already running) then I think your problem will go away

Function xlAppMyReturnExcelApp() As Excel.Application
'this works using the create/get object thing
'this is better than referring to excel.workbooks or whatever directly because
'that way is not gauranteed to release excel correctly
On Error Resume Next
Set xlAppMyReturnExcelApp = GetObject(, "Excel.Application&quot
If Err.Number = 429 Then
Set xlAppMyReturnExcelApp = CreateObject("Excel.Application&quot
Else
Err.Raise Err.Number
End If

End Function

So basically your calling code would look like this

Dim xlApp as excel.application

Set xlApp = xlAppMyReturnExcelApp

There doesn't seem to be anything wrong with your closing code, but it might help to close up like this

xlApp.ActiveWorkbook.Close
xlApp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

Since then you are releasing the objects in the reverse order they were created

Hope this solves your problem
Mark

 

[joel009]

Mark,
Will this use an already open instance of Excell and if none are open then open a new one?

Joel

 

[Custom24]

Yes (that's normally what you would want), though you can modify it to use a new instance regardless if you prefer. If so, in the function, you only need the line

Set xlAppMyReturnExcelApp = CreateObject("Excel.Application&quot

The key thing is that the use of GetObject or CreateObject seems to avoid the instance of Excel remaining open after you've finished with it.

Mark