WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
OLE COM ADO CDO ADSI LDAP
OLE with Excel
plus
Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Close Excel Object

Question:

The following code snipits open and close the associated Excel 2003 workbook successfully. However, when I use this on a machine that has Excel 2007 the Excel process remains open. Any suggestions on how to ensure that Excel closes? 
opening code...
DB = ObjectOpen("Excel.Application")
DB.Visible = @False
DB.UserControl = @TRUE
DB.DisplayAlerts = @FALSE ; Suggested by Jay & Marty 2/27/05
oAPP = DB.Workbooks
oAPP.Open("C:\mast25\qc\qctrend.xls")
oACT = DB.ActiveWorkbook
oWKS = DB.Worksheets
oWS = DB.Worksheets(1)
oWS.Activate

Closing Code...
oWS = DB.Worksheets(2)
DB.Goto("R3C1") 
db.activeworkbook.save()
DB.activeWorkbook.Save()
oWs = 0
db.quit()

Answer:

Try adding DB = 0 after the DB.Quit line of code.

User Reply:

I tried this without luck. The excel process remains open in the Task Manager after the winBatch script executes with this change.

Answer:

Odds are it is a Excel bug but based on the posted code, you are leaving several objects unreleased when you call the quit member.

If you haven't tried yet, it might be worthwhile to release all but the DB object before calling quit. Make sure to releases each object in the reverse order of acquisition. MORE: There have been quite a few blogs about 2007 'hanging'. Maybe try this: 

db.activeWorkbook.Close(::saveChanges=@True)
db.quit()
db=0

User Reply:

I have tried all of the suggested coding without success. Excel 2007 remains open as a process. Any other suggestions or work-arounds?

Answer:

Here is some code I want you to try: 
DebugTrace(@on,"trace.txt")
xls = "C:\Temp\Data\applist.xls" ;CHANGE TO FIT YOUR NEEDS
DB = ObjectOpen("Excel.Application")
DB.Visible = @False
DB.UserControl = @TRUE
DB.DisplayAlerts = @FALSE ; Suggested by Jay & Marty 2/27/05
oAPP = DB.Workbooks
oAPP.Open(xls)
oACT = DB.ActiveWorkbook
oWKS = DB.Worksheets
oWS = DB.Worksheets(1)
oWS.Activate

;Closing Code...
oWS = DB.Worksheets(2)
DB.Goto("R3C1") 
db.activeworkbook.save()
db.activeworkbook.Close()
oWs = 0
oWKS = 0
oApp = 0
db.quit()
db = 0
Please post the trace.txt file here.

User Reply:

Thank you for your help. The trace file contents are posted below. 
*** Debug Initialized ***

==============================
Thu 1/11/2007 11:25:46 AM
WinBatch 32 2005G
WIL DLL 5.9gei
C:\MAST25\Batch\SVH\KillExcel.wbt
Windows platform: NT, version: 5.1, build: 2600 (Service Pack 2)
ErrorMode: @CANCEL
==============================

xls = "C:\mast25\qc\qctrend.xls"
(0) VALUE STRING => "C:\mast25\qc\qctrend.xls"

DB = ObjectOpen("Excel.Application")
(6781) VALUE INT/COMOBJ => 2688916

DB.Visible = @False
(6797) VALUE VARIANT_BOOL => 0

DB.UserControl = @TRUE
(6797) VALUE VARIANT_BOOL => -1

DB.DisplayAlerts = @FALSE
(6812) VALUE VARIANT_BOOL => 0

oAPP = DB.Workbooks
(6828) VALUE INT/COMOBJ => 2696956

oAPP.Open(xls)
(12406) VALUE INT/COMOBJ => 0

oACT = DB.ActiveWorkbook
(12422) VALUE INT/COMOBJ => 2699108

oWKS = DB.Worksheets
(12453) VALUE INT/COMOBJ => 2701732

oWS = DB.Worksheets(1)
(12469) VALUE INT/COMOBJ => 2703172

oWS.Activate
(12469) VALUE VARIANT_BOOL => -1

oWS = DB.Worksheets(2)
(12469) VALUE INT/COMOBJ => 2705060

DB.Goto("R3C1")
(12750) VALUE VARIANT_BOOL => -1

db.activeworkbook.save()
(14969) VALUE VARIANT_BOOL => -1

db.activeworkbook.Close()
(15062) VALUE VARIANT_BOOL => -1

oWs = 0
(15078) VALUE INT => 0

oWKS = 0
(15078) VALUE INT => 0

oApp = 0
(15078) VALUE INT => 0

db.quit()
(15078) VALUE VARIANT_EMPTY => 

db = 0
(15140) VALUE INT => 0

--- Normal termination ---

;;;END OF JOB;;;

Answer:

The only thing i can suggest is to use Appexist to see if Excel is still tunning, then if so use Terminate APp or IntControl 56 to kill the Excel process.

As I mentioned before, there are substantive issues with Excel hanging

http://www.xtremevbtalk.com/showthread.php?p=1157109#post1157109

http://www.thescripts.com/forum/thread202826.html I'm just guessing here but you have tried everything suggested (all of which makes sense) so one assumption is that Excel 2007 has some baggage that needs to be cleaned up with respect to its COM interface.

A few years ago I had a similar problem with Excel. It turned out to be a conflict between a specific version of Norton Antivirus and Excel 2003. The fix was to upgrade Norton Antivirus to the next major version. This Norton Antivirus bug did not affect COM servers other than Excel. Not saying that it is the problem here but anti virus programs do aggressively hook into the Windows COM system for obvious reasons. Just something to keep in mind. 

Article ID:   W17448

File Created: 2008:04:10:15:10:36
Last Updated: 2008:04:10:15:10:36