Excel Object

I create Excel Book as an object from VB application. After that I need to close it and delete all its objects from memory. I tried:

objExcel.Quit
set objExcel = nothing



Book disappears, but Excel Main Window is still sitting in a memory (I'm checking it with Task Manager).
I tried

lngReturn = FindWindow("XLMAIN", vbNullString) '"XLMAIN"-is the name of Excel Window Class
lngReturn = PostMessage(lngReturn, &H10, 0, 0)



The same result - Excel disappears but continue to sit in a memory.
How can I stop Excel and clean a memory.
Thank you.
Vlad

[Chris Eastwood]

Are you sure that you have released all references to other objects in use in Excel ? What about any worksheet items ? Are they all closed and set to nothing ?

I just tried the following code :


Dim o as Excel.Application

set o = new Excel.Application

MsgBox o.OrganizationName

o.Quit
set o = nothing




- and all Excel was completely unloaded from memory after the 'Set o = Nothing'


Chris Eastwood

CodeGuru - the website for developers
http://codeguru.developer.com/vb

Thank you, Chris.
After I created Excel Object, I add a workbook:

With objExcel
.Workbooks.Add
on error resume next
'if there is already file with today's date in a name, delete it
Kill strBookName
'save Book with a name containing Today's date
.ActiveWorkbook.SaveAs FileName:=strBookName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=false _
, CreateBackup:=false
End With
'Create Header of SpreadSheet
Columns("B:B").ColumnWidth = 22.14
Columns("C:C").ColumnWidth = 22.29
Range("B1").Select
ActiveCell.FormulaR1C1 = "Bill Summary " & CStr(datToday)



I do something else here, but do not create (at least I think so) any objects explicitly.
After I ran previous procedure I'm trying to do next:

Do While objExcel.Workbooks.Count > 0
objExcel.Workbooks.Close
Loop
objExcel.Quit
set objExcel = nothing



In debug mode I see that objExcel was closed and set to nothing, but if I run this:

lngReturn = FindWindow("XLMAIN", vbNullString) '"XLMAIN"-is the name of Excel Window Class

If lngReturn > 0 then
MsgBox "Excel is running. Close it and restart this Program before to run Summary.", vbCritical, _
"Close EXCEL"
Exit Sub
End If



I receive my message and using Task Manager I see excel.exe in a memory.
What's wrong?
I have to close everything after user ran all the procedures once, otherwise second run gives a bad result (Excel doesn't create the Workbook with the name I want, it just creates it with the name Book2, Book3 and so on, depending on how many times those procedures have been running.
Thank you.
Vlad

[Chris Eastwood]

There's a couple of things that stike me about your code :

1. the :
on error resume next


- call

This might be fine for deleting the file and then ignoring an error if it doesn't exist, but the 'resume next' error handler is still in use throughout your code - I'd recomment putting some proper handling in that routine just in case.

2. Could it be possible that VB is somehow keeping a reference to the Excel Object alive ? For instance, do you have this project loaded by itself, or in a group ? A user control can cause DLL's and other objects to be retained in memory when you are debugging with the IDE, yet they work fine when you compile and run them.

I'd recommend putting some proper error handling code in and then trying your program compiled just to make sure.



Chris Eastwood

CodeGuru - the website for developers
http://codeguru.developer.com/vb

Chris, I compiled, but have the same result. When I'm stopping project (in VB) and run again, everything is fine which means for me, that VB itself doesn't keep any references to Excel. Project does keep somehow. With EXE I have the same result.
Vlad