Quitting excel but still present in memory...

[Phailak]

Hail,

I have a vb app that uses excel as printing. What I do is create an object in excel, paste the stuff from my SQL recordset, then print, than quit without saving. I clearly state obj.quit at the end of each printing session but my excel remains in memory none the less in my task manager??? Is there something more than Quit I must do?

Phailak

 

[waldemar]

I have the same problem right now:

excel.Application.DisplayAlerts = True
excel.ActiveWorkbook.Close False
excel.Application.Quit

(while "excel" is the excel application object) doesn't clear excel from the task manager. I noticed it since the next time I run a similar procedure I get shitloads of error messages.... How can Excel properly removed from the memory?

 

[RobBroekhuis]

That doesn't sound right. Here's a snippet of code that creates an Excel object, does some stuff, then quits. On my computer, it leaves no trace.

Sub CallExcel()
Dim ExcelApp As Excel.Application
Set ExcelApp = New Excel.Application
ExcelApp.Workbooks.Add
ExcelApp.ActiveCell = "Hello"
MsgBox ExcelApp.ActiveCell
ExcelApp.DisplayAlerts = False
ExcelApp.Quit

End Sub
Rob

 

[waldemar]

Thanks Rob, but.... What doesnt sound right? Looks like the same to me? (Except I need the alerts since I want the user to be able to save changes)

 

[waldemar]

It feels like the Quit Command is just not executed... Strange thing: As soon as I close the Access Application the Background Excel also vanishes... like...like something is still open and it is closed automatically on the access quit cleanup.... ?????

 

[RobBroekhuis]

I didn't mean that your code wasn't right; I meant, it doesn't sound right that Excel would hang around. My code was executed from Word - I don't have Access. Maybe Access is different for some reason? What happens if you do turn the displayalerts to false? Maybe Excel thinks it's waiting for some user input?
Is your Excel application visible?
Rob
Rob

 

[sfvb]

I tried Rob's code from Excel and from Access. I tried it with ExcelApp.DisplayAlerts = False left in the code and with it commented out. I also tried it without using the New keyword (See code below).

In all cases the OS released the process. When stepping through the code, both Rob's code and my code started the EXCEL.EXE process after the Set command exeuted. Rob's code released the process after the End Sub executed. The code I used released the process after the Set xlApp = Nothing executed.

I know from prior posts that other people are having the same problem that Phailak and Waldemar are having. I'm curious as to what the problem is. So, in case it's OS or service pack related, I'm using:[ul][li]Windows 2000 (Service Pack 3)
[/li][li]Office 2000 premium (Service Release 1)
[/li][li]VBA version 6.00
[/li][li]ADO version 2.7
[/li][li]Jet Version 4.0[/li][/ul](I doubt this is related but, I also have Visual Studio 6.0 (Service Pack 5) installed.)

Sub TestForTaskManager()
   Dim xlApp As Excel.Application
      
   Set xlApp = CreateObject("Excel.Application")
   xlApp.Visible = True
   xlApp.DisplayAlerts = False
   xlApp.Workbooks.Add
   xlApp.Range("A1").Formula = "Test"
   xlApp.ActiveWorkbook.SaveAs FileName:="c:\MyTest.xls", FileFormat:=xlNormal       
   xlApp.Quit
   Set xlApp = Nothing
End Sub

 

[Phailak]

Hail,

If it helps, here are my specs:

Windows 2000 (Service Pack 3)

Office 2000

VBA version 5.00

ADO version ?

Jet Version 3.5

I got around my problem by using access instead of excel (I was using excel to print a report in my own format)

Phailak

 

[waldemar]

I tried a solution from another thread. It seeeems to work now:

Excel seems to be a bit picky (or I a little bit sloby) about excel objects one creates while remote working with excel. Releasing all the objects (=Nothing) in the reverse order of setting allowed .Quit to kind of "end" excel properly. The Range object (although it should be a property of the access application) seems to very sensitive to this.

Interesting: The Excel Process (Windows 2000, Office 2000) seems to stay from now on allowing further COM work. Manually ending that process disables Access to create any further Excel objects...

 

[hamzajosh]

can you give me the address of the other thread because i have the same problem and releasing in reverse order of setting does not work for me. I am calling excel from word macro.

 

[borgunit]

I use this when Excel is public to close it. It may work for you.


Public Function CloseExcel()

Set Workbook = Nothing
Set WorkSheet = Nothing
Set Workbook2 = Nothing
Set WorkSheet2 = Nothing

If Not ExcelApp Is Nothing Then
ExcelApp.Quit
End If
Set ExcelApp = Nothing

End Function

 

[waldemar]

There is in interesting function in this thread (close to the end) that uses the Timer to force Excel to quit:

thread707-140366

possible Excel Quit solutions...:

thread707-289181

 

[StewartJ]

The code samples all include the key line :

Set xlApp = Nothing

If you don't do this, there still is a refernce to it and the app will remain in memory. In the case of Word, you MUST .Quit first as well. I don't know about Excel but it would be good practice anyway.

As a general point in VB, if you don't set your references to Nothing once you have finished with them, you will generate a system resource leak.

 

[RobBroekhuis]

Regarding the system resource leak - does that hold even for simple objects such as ranges that are dim'd locally in a sub? While I set to nothing larger objects such as applications, I don't normally do it with simple objects - nor do I see other VBA programmers routinely do it.
Rob

 

[StewartJ]

It depends what you mean. Variables once declared remain until they go out of scope but it's not worth worrying about that. I would include range objects in the 'variables' category. If you are thinking about API objects such as device contexts, then they will stay around in the same way but sometimes you are required to leave them for the system housekeeping to take care of - you would need to check the API documentation for that.

 

[rmikesmith]

Rob,

If I have declared an object variable globally then I will explicitly set this to Nothing when finished with it (at the end of a Sub that uses it, for example). If the object variable is declared local to a Sub or Function then I typically do not, as it will go out of scope with the Sub or Function's termination.


Regards,
Mike

 

[mrrawdeal]

I had the same problem as Phailka did and used the following code below after saving the file to close excel



SendKeys "%{F4}", True

It send the Alt-F4 key to excel to close excel

 

[1234123412341234]

Hi guys, just want to share my solution, it work.

Everytime you use ANY function of Excel, declare OBJECTS.
Here's an example :

Public Function GetCellValue(ByVal sCellule As String) As String
If bIsOpen Then
Dim rRange As Excel.Range
Set rRange = XLSheet.Range(sCellule)

Dim wsf As WorksheetFunction
Set wsf = XL.WorksheetFunction

If wsf.IsText(rRange.value) Or _
wsf.IsNumber(rRange.value) Then
GetCellValue = rRange.value
Else
GetCellValue = " " 'Cellule vide
End If

Set wsf = Nothing
Set rRange = Nothing

End If
End Function


As you can see, i have made a Range object and a wsf one. If i didn't make the wsf one, Excel would not close with Application.quit.

Also notice that all the object i have created are set to nothing at the end. IT IS ESSENTIAL, or you'll have the same problem.

Notice that XlSheet is an object too and Xl, and xlBook.
Everything you use must be an object EVERYTHING, no jokes.

So good luck guys.

-=Apocalypse=-
Apocalypse_ACD@hotmail.com