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 ######

Edited 3 Years Ago by Dani: Formatting fixed

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

Edited 3 Years Ago by TnTinMN

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

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.

This article has been dead for over six months. Start a new discussion instead.