Excel Process will not end

Hello everyone, I have done some research (NG/google), and I have yet to
find a solution to this problem.

From ACAD, when an Excel object is created, and a file is opened , there is
an Excel process running in the Task Manager.

Senario.
In-ACAD, user clicks button to (which calls VBA) to open an Excel database.
When user is done with database, user closes Excel.
Excel process is still running in Task Manager. Why? How do I get rid of
it?

Solutions I have seen is to make sure objects are set to Nothing.
Been there, done that.


So I ran a test to open an Excel file, and close it immediately, and the
Excel process still will not end until I close AutoCAD.

Thanks for any help,
Dan


Code example below:

Sub Open_CIM()
Dim excelApp As Excel.Application
Dim wbkObj As Workbook
Dim shtObj As Worksheet
On Error Resume Next
Err.Clear
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application") '
If Err <> 0 Then
MsgBox "Could not start Excel.", vbExclamation
End
End If
End If
AppActivate ThisDrawing.Application.Caption
excelApp.Visible = True
Workbooks.Open FileName:= _
("c:\Test\CIMs.xls"), Origin _
:=xlWindows
Sheets("CIM").Visible = True
ActiveWorkbook.Close SaveChanges:=False
excelApp.Quit
'Clean-up
Set excelApp = Nothing
Set wbkObj = Nothing
Set shtObj = Nothing
End Sub

 

You have to make sure that when you set them to Nothing, you do it in the
reverse order that they get created. If attempt to set the App object to
Nothing before you release the doc object, it won't be successful, no error,
just not successful.

 

The reason Excel is not close (but closes when AutoCAD is closed) is exactly
your code does:

You declared three variables to refer Excel.Application, Workbooks and
Worksheet objects (excelApp, wkbObjec and shtObj), but only excelApp is
actually used to refer an instance of object because of the GetObject() and
CreateObject() code. After the Excel.Application object is created, instead
of using wkbObj and shtObj to refer Workbooks and Worksheet, you used GLOBAL
VARIABLE in VBA "Workbooks", "Sheets" and "ActiveWorkbook" to open/close a
xls file. Thus, although you set exceApp/wkbObj/shtObj to Nothing (note,
wkbObj and shtObj is nothing from the beginning and never being pointed to
an object instance), global variable "Workbooks", "Sheets" abd
"ActiveWorkbooks" are still in scope until the AutoCAD session is close.

It is never a good idea to use global variable "Workbooks" to do what you
did. VB/VBA sometimes does to much so that we VB/VBA programmers are often
do not see some obvious mistake. In this case, if you set reference to Excel
object library, you can use "Workbooks"/"Sheets"/ActiveWorkbook"... directly
without declare them.

So your code should be:

Sub Open_CIM()
Dim excelApp As Excel.Application
Dim wbkObj As Workbook
Dim shtObj As Worksheet
On Error Resume Next
Err.Clear
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application") '
If Err <> 0 Then
MsgBox "Could not start Excel.", vbExclamation
End
End If
End If
AppActivate ThisDrawing.Application.Caption
excelApp.Visible = True

'''' Workbooks.Open FileName:= _ '''Wrong code here
'''' ("c:\Test\CIMs.xls"), Origin _

Set wkbObj=excelApp.Workbooks.Open (FileName:= _
"c:\Test\CIMs.xls", Origin _
:=xlWindows)
'''' Sheets("CIM").Visible = True '''Wrong code here

Set shtObj=wkbObj.Sheets("CIM")

'''' ActiveWorkbook.Close SaveChanges:=False '''Wrong code here

wkbObj.Close False
excelApp.Quit

'Clean-up

''' You do not exactly need the code below,
''' but most people just do it, no harm anyway.
Set excelApp = Nothing
Set wbkObj = Nothing
Set shtObj = Nothing

End Sub

 

Combine Ed's and Norman's responses and add this to them and you've got
your answer:

If Not shtObj Is Nothing Then Set shtObj = Nothing
If Not wbkObj Is Nothing Then Set wbkObj = Nothing
If Not excelApp Is Nothing Then Set excelApp = Nothing

You should *always* explicitly clear out your object variables.


-- Mike
___________________________
Mike Tuersley
CADalyst's CAD Clinic
Rand IMAGINiT Technologies
___________________________
the trick is to realize that there is no spoon...

 

Perfect! I combine all three answers, and that fixes my problem. Thank you
everyone. I have successfully applied the modifications to all my apps with
excel, and learned better habits too.

Have a great week,
Dan