Excel.exe application remains open in task manager

[atletico]

Hello guys,

I would appreciate if you could help my on the below issue.

When i used an Excel object in my code, when closing this, the excel.xls applicataion remains opened at the task manager.
i am copying below the test code for your reference, which i am using in order to reproduce the same so any help
would really appreciated.

Also i noticed the below: When i comment out the following
"exWB.saveAs "filepath""
the excel.exe instance is closing propertly. But when i am including this again in the project, in order to be able to save the excel, again the excel.exe remains in the task manager even if i closed the object.

Thanks.

----------##### Used Code ######

Private Sub Command1_Click()

Dim ex As Excel.Application
Dim exWB As Excel.Workbook
'Dim exSheet As Excel.Worksheet

Set ex = CreateObject("Excel.Application")
Set exWB = ex.Workbooks.Add
exWB.Sheets(1).Range("a1").Value = "TEST"
'Set exSheet = exWB.ActiveSheet

'exSheet.Cells(1, 1).Value = "TEST"
'ex.ActiveWorkbook.SaveAs "C:\temp\temp.xls"
exWB.SaveAs "C:\temp\temp.xls"

'Set exWB = ex.Workbooks.Open("C:\temp\temp.xls")
Set exWB = ex.Workbooks.Add("C:\temp\temp.xls")

ex.Visible = True

'ex.Workbooks.Application.SaveWorkspace "C:\temp\temp.xls"
'exWB.SaveAs "C:\temp\temp.xls"
ex.UserControl = True
exWB.Close
ex.Workbooks.Close
ex.DisplayAlerts = False
ex.Application.Quit

Set exWB = Nothing
Set ex = Nothing


End Sub
----------##### Used Code ######

[TnTinMN]

When referencing the ComObjects(application, workbook, range, etc), follow the No Double ComObject rule (also referred to as the One-Dot rule):

DeclaredRef.Comobject.PropertOrMethod

not:

DeclaredRef.ComObject.ComObject.Property

You did a good job referencing and releasing most of them, but missed one.

try changing as follows:

Set ex = CreateObject("Excel.Application")
Set exWB = ex.Workbooks.Add
' in the next line you have created a inaccessible ComObject (Sheets(1))
'exWB.Sheets(1).Range("a1").Value = "TEST"
' change to
Set sheet1 = exWB.Sheet(1)
sheet1.Range("a1").Value = "TEST"
Set sheet1 = Nothing

[Reverend Jim]

OK. I thought I had this figured out. Based on the name (One-Dot Rule) don't both of the following violate that rule?

DeclaredRef.Comobject.PropertOrMethod           2 dots
DeclaredRef.ComObject.ComObject.Property        3 dots

or

exWB.Sheets(1).Range("a1").Value = "TEST"       3 dots
sheet1.Range("a1").Value = "TEST"               2 dots

or am I totally misinterpreting the "one dot" portion of the rule name? Or is it just a very bad choice for a rule name? Perhaps "one-dot rule" is zero-relative ;-P

[TnTinMN]

Or is it just a very bad choice for a rule name? Perhaps "one-dot rule" is zero-relative ;-P

You got-it. :)

I never understood the name very well myself, but that's what I've seen it called by many or the no-double dot rule as well. That's why I just decide to rename it it the above post. :) Possibly the original coiner of the phrase meant that there should be only One-Dot between the ComObject and a point of reference? I guess that makes sense.

Essentially anything that you reference in the a.b.c sequence that returns a ComObject should be set to its own variable so you can break the link to your application by setting the variable to Nothing.

exWB.Sheets(1).Range("a1").Value = "TEST"

In this, both sheets(1) and range("a1") return a ComObject. I just realized I FUBAR'd my original response to the OP. It should have been,

    Set ex = CreateObject("Excel.Application")
    Set exWB = ex.Workbooks.Add
    ' in the next line you have created a inaccessible ComObject (Sheets(1))
    'exWB.Sheets(1).Range("a1").Value = "TEST"
    ' change to
    Set sheet1 = exWB.Sheet(1)
    Set rng=sheet1.Range("a1")
    rng.Value = "TEST"
    Set rng = Nothing
    Set sheet1 = Nothing

Hopefully, I have not confused you and the OP further.

I know that I have not been the most coherent lately. Too many late nights working on my pet projects.:> Also, my frustation level has been high lately dealing with a bad mouse; at first I thought my machine had picked up a virus, or worse the USB bus was failing. I guess I wore out the left clicker. Selecting text has been a real joy!. Fixed that today, bought two new ones.

I'll miss the little bugger after using him for almost eight years.

[Reverend Jim]

Rest assured. It would not be possible to confuse me further. Thanks again for the explanation. I really think I got it this time.