Close the entire application of excel

[mpower]

Can you please remove the function call _Excel_RangeRead? We then know it is absolutely not UDF related.

expandcollapse popup

0 Excel instance(s) running.
Started Processing C:\Temp\Test.xlsx
_Excel_Open: @error = 0, @extended = 0
_Excel_BookOpen: @error = 0, @extended = 0
_Excel_BookClose: @error = 0, @extended = 0
_Excel_Close: @error = 0, @extended = 0
Finished Processing C:\Temp\Test.xlsx
1: 1 Excel instance(s) still running.
2: 1 Excel instance(s) still running.
3: 1 Excel instance(s) still running.
4: 1 Excel instance(s) still running.
5: 1 Excel instance(s) still running.
6: 1 Excel instance(s) still running.
7: 1 Excel instance(s) still running.
8: 1 Excel instance(s) still running.
9: 1 Excel instance(s) still running.
10: 1 Excel instance(s) still running.
11: 1 Excel instance(s) still running.
12: 1 Excel instance(s) still running.
13: 1 Excel instance(s) still running.
14: 1 Excel instance(s) still running.
15: 1 Excel instance(s) still running.
16: 1 Excel instance(s) still running.
17: 1 Excel instance(s) still running.
18: 1 Excel instance(s) still running.
19: 1 Excel instance(s) still running.
20: 1 Excel instance(s) still running.
21: 1 Excel instance(s) still running.
22: 1 Excel instance(s) still running.
23: 1 Excel instance(s) still running.
24: 1 Excel instance(s) still running.
25: 1 Excel instance(s) still running.
26: 1 Excel instance(s) still running.
27: 1 Excel instance(s) still running.
28: 1 Excel instance(s) still running.
29: 1 Excel instance(s) still running.
30: 1 Excel instance(s) still running.
31: 1 Excel instance(s) still running.
32: 1 Excel instance(s) still running.
33: 1 Excel instance(s) still running.
34: 1 Excel instance(s) still running.
35: 1 Excel instance(s) still running.
36: 1 Excel instance(s) still running.
37: 1 Excel instance(s) still running.
38: 1 Excel instance(s) still running.
39: 1 Excel instance(s) still running.
40: 1 Excel instance(s) still running.
41: 1 Excel instance(s) still running.
42: 1 Excel instance(s) still running.
43: 1 Excel instance(s) still running.
44: 1 Excel instance(s) still running.
45: 1 Excel instance(s) still running.
46: 1 Excel instance(s) still running.
47: 1 Excel instance(s) still running.
48: 1 Excel instance(s) still running.
49: 1 Excel instance(s) still running.
50: 1 Excel instance(s) still running.

 

[water]

Our problem seems to be common with .NET apps. I will check if I find a reference for pure VB as well.

[water]

Seems Excel doesn't like double dot notation. "chaining" object references seems to be a problem.
So I changed the code to only have single dot notation. Could you please test this version?
 

expandcollapse popup

#include <Excel.au3>
$file = "C:\Temp\Test.xlsx"
$aProcesses = ProcessList("Excel.exe")
ConsoleWrite($aProcesses[0][0] & " Excel instance(s) running." & @CRLF)
ConsoleWrite("Started Processing " & $file & @CRLF)
;--------------------- $oExcel = _Excel_Open()
$oExcel = ObjCreate("Excel.Application")
ConsoleWrite("_Excel_Open: @error = " & @error & ", @extended = " & @extended & @CRLF)
$oExcel.Visible = True
$oExcel.DisplayAlerts = True ; Default is False
$oExcel.ScreenUpdating = True
$oExcel.Interactive = True
;--------------------- $oWorkbook = _Excel_BookOpen($oExcel, $file, True)
$oWorkbooks = $oExcel.Workbooks
$oWorkbook = $oWorkbooks.Open($File, Default, True)
ConsoleWrite("_Excel_BookOpen: @error = " & @error & ", @extended = " & @extended & @CRLF)
;---------------------
; $aOrgArray = _Excel_RangeRead($oWorkbook)
; ConsoleWrite("_Excel_RangeRead: @error = " & @error & ", @extended = " & @extended & @CRLF)
;--------------------- _Excel_BookClose($oWorkbook, False)
$oWorkbook.Close()
ConsoleWrite("_Excel_BookClose: @error = " & @error & ", @extended = " & @extended & @CRLF)
$oWorkbook = 0
;--------------------- _Excel_Close($oExcel, False, True)
$oExcel.Quit()
ConsoleWrite("_Excel_Close: @error = " & @error & ", @extended = " & @extended & @CRLF)
$oExcel = 0
$oWorkbooks = 0
$oWorkbook = 0
;just to allow some time for the process to definitely close (if it does close)
ConsoleWrite("Finished Processing " & $file & @CRLF)
For $i = 1 To 50
    Sleep(10)
    $aProcesses = ProcessList("Excel.exe")
    If $aProcesses[0][0] = 0 Then ExitLoop
    ConsoleWrite($i & ": " & $aProcesses[0][0] & " Excel instance(s) still running." & @CRLF)
Next

 

[mpower]

Result, unfortunately, is the same with above code:

expandcollapse popup

0 Excel instance(s) running.
Started Processing C:\Temp\Test.xlsx
_Excel_Open: @error = 0, @extended = 0
_Excel_BookOpen: @error = 0, @extended = 0
_Excel_BookClose: @error = 0, @extended = 0
_Excel_Close: @error = 0, @extended = 0
Finished Processing C:\Temp\Test.xlsx
1: 1 Excel instance(s) still running.
2: 1 Excel instance(s) still running.
3: 1 Excel instance(s) still running.
4: 1 Excel instance(s) still running.
5: 1 Excel instance(s) still running.
6: 1 Excel instance(s) still running.
7: 1 Excel instance(s) still running.
8: 1 Excel instance(s) still running.
9: 1 Excel instance(s) still running.
10: 1 Excel instance(s) still running.
11: 1 Excel instance(s) still running.
12: 1 Excel instance(s) still running.
13: 1 Excel instance(s) still running.
14: 1 Excel instance(s) still running.
15: 1 Excel instance(s) still running.
16: 1 Excel instance(s) still running.
17: 1 Excel instance(s) still running.
18: 1 Excel instance(s) still running.
19: 1 Excel instance(s) still running.
20: 1 Excel instance(s) still running.
21: 1 Excel instance(s) still running.
22: 1 Excel instance(s) still running.
23: 1 Excel instance(s) still running.
24: 1 Excel instance(s) still running.
25: 1 Excel instance(s) still running.
26: 1 Excel instance(s) still running.
27: 1 Excel instance(s) still running.
28: 1 Excel instance(s) still running.
29: 1 Excel instance(s) still running.
30: 1 Excel instance(s) still running.
31: 1 Excel instance(s) still running.
32: 1 Excel instance(s) still running.
33: 1 Excel instance(s) still running.
34: 1 Excel instance(s) still running.
35: 1 Excel instance(s) still running.
36: 1 Excel instance(s) still running.
37: 1 Excel instance(s) still running.
38: 1 Excel instance(s) still running.
39: 1 Excel instance(s) still running.
40: 1 Excel instance(s) still running.
41: 1 Excel instance(s) still running.
42: 1 Excel instance(s) still running.
43: 1 Excel instance(s) still running.
44: 1 Excel instance(s) still running.
45: 1 Excel instance(s) still running.
46: 1 Excel instance(s) still running.
47: 1 Excel instance(s) still running.
48: 1 Excel instance(s) still running.
49: 1 Excel instance(s) still running.
50: 1 Excel instance(s) still running.

 

[water]

Verdammte S...
Now I'm soon running out of ideas

[mpower]

@water, thanks for your help so far, at least we pinned down that it's not a UDF bug.

For now I will use a crude workaround by closing the most recent Excel.exe instance:

#include <Excel.au3>
$file = "C:\Temp\Test.xlsx"
$aProcesses = ProcessList("Excel.exe")
ConsoleWrite($aProcesses[0][0] & " Excel instance(s) running." & @CRLF)
ConsoleWrite("Started Processing " & $file & @CRLF)
;--------------------- $oExcel = _Excel_Open()
$oExcel = ObjCreate("Excel.Application")
ConsoleWrite("_Excel_Open: @error = " & @error & ", @extended = " & @extended & @CRLF)
$oExcel.Visible = True
$oExcel.DisplayAlerts = True ; Default is False
$oExcel.ScreenUpdating = True
$oExcel.Interactive = True
;--------------------- $oWorkbook = _Excel_BookOpen($oExcel, $file, True)
$oWorkbooks = $oExcel.Workbooks
$oWorkbook = $oWorkbooks.Open($File, Default, True)
ConsoleWrite("_Excel_BookOpen: @error = " & @error & ", @extended = " & @extended & @CRLF)
;---------------------
$aOrgArray = _Excel_RangeRead($oWorkbook)
ConsoleWrite("_Excel_RangeRead: @error = " & @error & ", @extended = " & @extended & @CRLF)
;--------------------- _Excel_BookClose($oWorkbook, False)
$oWorkbook.Close()
ConsoleWrite("_Excel_BookClose: @error = " & @error & ", @extended = " & @extended & @CRLF)
$oWorkbook = 0
;--------------------- _Excel_Close($oExcel, False, True)
$oExcel.Quit()
ConsoleWrite("_Excel_Close: @error = " & @error & ", @extended = " & @extended & @CRLF)
$oExcel = 0
$oWorkbooks = 0
$oWorkbook = 0
$aProcesses = ProcessList("Excel.exe")
ProcessClose($aProcesses[$aProcesses[0][0]][1])
Sleep(100) ;just to allow some time for the process to definitely close (if it does close)
$aProcesses = ProcessList("Excel.exe")
ConsoleWrite($aProcesses[0][0] & " Excel instance(s) still running." & @CRLF)
ConsoleWrite("Finished Processing " & $file & @CRLF)

My result:

0 Excel instance(s) running.
Started Processing C:\Temp\Test.xlsx
_Excel_Open: @error = 0, @extended = 0
_Excel_BookOpen: @error = 0, @extended = 0
_Excel_RangeRead: @error = 0, @extended = 0
_Excel_BookClose: @error = 0, @extended = 0
_Excel_Close: @error = 0, @extended = 0
0 Excel instance(s) still running.
Finished Processing C:\Temp\Test.xlsx

 

Edited August 4, 2015 by mpower
cleaned up.

[water]

One last idea would be to try a VB script. If it runs fine then it is an issue with AutoIt, else with Excel.
Will post an example soon.

[JohnOne]

I think your example was flawed, try this it works and closes fine for me.

EDIT:

never mind, my mistake.

expandcollapse popup

#include <Excel.au3>
$file = @ScriptDir & "\Catalogue.xlsx"
$aProcesses = ProcessList("Excel.exe")
ConsoleWrite($aProcesses[0][0] & " Excel instance(s) running." & @CRLF)
ConsoleWrite("Started Processing " & $file & @CRLF)
;--------------------- $oExcel = _Excel_Open()
$oExcel = ObjCreate("Excel.Application")
ConsoleWrite("_Excel_Open: @error = " & @error & ", @extended = " & @extended & @CRLF)
$oExcel.Visible = True
$oExcel.DisplayAlerts = True ; Default is False
$oExcel.ScreenUpdating = True
$oExcel.Interactive = True
;--------------------- $oWorkbook = _Excel_BookOpen($oExcel, $file, True)
$oWorkbooks = $oExcel.Workbooks
$oWorkbook = $oWorkbooks.Open($file, Default, True)
ConsoleWrite("_Excel_BookOpen: @error = " & @error & ", @extended = " & @extended & @CRLF)
;---------------------
; $aOrgArray = _Excel_RangeRead($oWorkbook)
; ConsoleWrite("_Excel_RangeRead: @error = " & @error & ", @extended = " & @extended & @CRLF)
;--------------------- _Excel_BookClose($oWorkbook, False)
$oWorkbook.Close()
ConsoleWrite("_Excel_BookClose: @error = " & @error & ", @extended = " & @extended & @CRLF)
$oWorkbook = 0
;--------------------- _Excel_Close($oExcel, False, True)
$oExcel.Quit()
ConsoleWrite("_Excel_Close: @error = " & @error & ", @extended = " & @extended & @CRLF)
$oExcel = 0
$oWorkbooks = 0
$oWorkbook = 0
;just to allow some time for the process to definitely close (if it does close)
ConsoleWrite("Finished Processing " & $file & @CRLF)
For $i = 1 To 500
    Sleep(10)
    $aProcesses = ProcessList("Excel.exe")
    If $aProcesses[0][0] = 0 Then
        ExitLoop
    EndIf
    ConsoleWrite($i & ": " & $aProcesses[0][0] & " Excel instance(s) still running." & @CRLF)
Next

$aProcesses = ProcessList("Excel.exe")
MsgBox(0,0, $aProcesses[0][0] & " Excel instance(s) still running." & @CRLF) ; keep script alive

 

Edited August 4, 2015 by JohnOne

[water]

This little VBS shows the same problem: Excel hangs until the script has ended.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbooks = objExcel.Workbooks
Set objWorkbook = objWorkbooks.Open("C:\temp\test.xlsx")
objWorkbook.Close()
objWorkbook = 0 
objExcel.Quit()
objExcel = 0
MsgBox("Finished!")
WScript.Quit

Is this true for you too?

Edited August 4, 2015 by water

[water]

This works for me and closes the Excel process as soon as the Excel variables have been freed:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbooks = objExcel.Workbooks
Set objWorkbook = objWorkbooks.Open("C:\temp\test.xlsx")
WScript.Echo "Workbook opened!"
objWorkbook.Close()
WScript.Echo "Workbook closed!"
objWorkbooks = 0
objWorkbook = 0 
WScript.Echo "Workbook variables freed!"
objExcel.Quit()
WScript.Echo "Excel closed!"
objExcel = 0
WScript.Echo "Excel variables freed!"
Wscript.Sleep 20000
MsgBox("Finished!")
WScript.Quit

 

[water]

This is the AutoIt code for the VB above and it closes Excel as soon as the Excel variables have been released:

$objExcel = ObjCreate("Excel.Application")
$objWorkbooks = $objExcel.Workbooks
$objWorkbook = $objWorkbooks.Open("C:\temp\test.xlsx")
msgBox(0, "Info", "Workbook opened!")
$objWorkbook.Close()
msgBox(0, "Info", "Workbook closed!")
$objWorkbooks = 0
$objWorkbook = 0
msgBox(0, "Info", "Workbook variables freed!")
$objExcel.Quit()
msgBox(0, "Info", "Excel closed!")
$objExcel = 0
msgBox(0, "Info", "Excel variables freed!")
Sleep(20000)
MsgBox(0, "", "Finished!")
Exit

 

Edited August 4, 2015 by water

[JohnOne]

I still cannot reproduce, your code from #63

1: 1 Excel instance(s) still running.
2: 1 Excel instance(s) still running.
3: 1 Excel instance(s) still running.
4: 1 Excel instance(s) still running.
5: 1 Excel instance(s) still running.
6: 1 Excel instance(s) still running.
7: 1 Excel instance(s) still running.
8: 1 Excel instance(s) still running.
9: 1 Excel instance(s) still running.
10: 1 Excel instance(s) still running.

[water]

On your and my installation the code from #63 runs just fine. My console shows that Excel has been closed after 7 loops.
But on mpower's installation it never ends.

[JohnOne]

If your script opens more than one instance of excel then something like this might be a workaround...

#include <Array.au3>

$aProc1 = ProcessList("Notepad.exe")
_ArrayDisplay($aProc1)

Run("Notepad")
Sleep(1000)

$aProc2 = ProcessList("Notepad.exe")
_ArrayDisplay($aProc2)

If $aProc1[0][0] < $aProc2[0][0] Then
    For $i = UBound($aProc1) To $aProc2[0][0]
        ProcessClose($aProc2[$i][1])
    Next
EndIf

Sleep(1000)

$aProc3 = ProcessList("Notepad.exe")
_ArrayDisplay($aProc3)

 

[mpower]

 

This little VBS shows the same problem: Excel hangs until the script has ended.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbooks = objExcel.Workbooks
Set objWorkbook = objWorkbooks.Open("C:\temp\test.xlsx")
objWorkbook.Close()
objWorkbook = 0 
objExcel.Quit()
objExcel = 0
MsgBox("Finished!")
WScript.Quit

Is this true for you too?

Above vbscript does also leave Excel.exe hanging after finishing.

This is the AutoIt code for the VB above and it closes Excel as soon as the Excel variables have been released:

$objExcel = ObjCreate("Excel.Application")
$objWorkbooks = $objExcel.Workbooks
$objWorkbook = $objWorkbooks.Open("C:\temp\test.xlsx")
msgBox(0, "Info", "Workbook opened!")
$objWorkbook.Close()
msgBox(0, "Info", "Workbook closed!")
$objWorkbooks = 0
$objWorkbook = 0
msgBox(0, "Info", "Workbook variables freed!")
$objExcel.Quit()
msgBox(0, "Info", "Excel closed!")
$objExcel = 0
msgBox(0, "Info", "Excel variables freed!")
Sleep(20000)
MsgBox(0, "", "Finished!")
Exit

 

Alas, even the above code leaves an Excel.exe hanging after the script ends

If your script opens more than one instance of excel then something like this might be a workaround...

#include <Array.au3>

$aProc1 = ProcessList("Notepad.exe")
_ArrayDisplay($aProc1)

Run("Notepad")
Sleep(1000)

$aProc2 = ProcessList("Notepad.exe")
_ArrayDisplay($aProc2)

If $aProc1[0][0] < $aProc2[0][0] Then
    For $i = UBound($aProc1) To $aProc2[0][0]
        ProcessClose($aProc2[$i][1])
    Next
EndIf

Sleep(1000)

$aProc3 = ProcessList("Notepad.exe")
_ArrayDisplay($aProc3)

 

See my post #66

Edited August 4, 2015 by mpower

[water]

Do you have any Addons installed in Excel? I once had a similar problem with Word Addons.

[JohnOne]

See my post #66

I did, it closes one instance of Excel.

[water]

Above vbscript does also leave Excel.exe hanging after finishing.

I know. I didn't release objWorkbooks so Excel didn't close until the script ended. What's weird is, that in your environment ending the script doesn't shut down Excel.
I assume that something else (outside the script) keeps Excel up and running - maybe a badly written Addon.

[mpower]

I know. I didn't release objWorkbooks so Excel didn't close until the script ended. What's weird is, that in your environment ending the script doesn't shut down Excel.
I assume that something else (outside the script) keeps Excel up and running - maybe a badly written Addon.

@water, You are AWESOME, thank you! The offender was indeed an Addon - WatchDox - a document protection Addon (highly annoying one and I only had to install it as some certification material I was studying required it).

As soon as I removed the WatchDox Addon, Excel started closing properly!

Woo Hoo!

P.S. I've reported the issue to the Watchdox team @ Blackberry. Hopefully they will investigate and fix their Addon.

Edited August 4, 2015 by mpower

[water]

I'm glad that the problem finally could be solved
I hate it when things don't work - and I don't know why!