Close the entire application of excel

[JohnOne]

Problem for me was I was not supplying full path to BookOpen.

[water]

Thanks for testing!
Unfortunately I'm still not able to reproduce the problem with the "hanging" Excel process

[JohnOne]

I can reproduce the error still with....

#include <Excel.au3>

$oExcel1 = _Excel_Open()
ConsoleWrite("_Excel_Open " & @error & " " & @extended & @LF)
$oWorkbook = _Excel_BookOpen($oExcel1, "Catalogue.xlsx")
ConsoleWrite("_Excel_BookOpen " & @error & " " & @extended & @LF)
_Excel_BookSaveAs($oWorkbook, @ScriptDir & "\Catalogue2.xls", $xlExcel8)
ConsoleWrite("_Excel_BookSaveAs " & @error & " " & @extended & @LF)
_Excel_BookClose($oWorkbook, False)
ConsoleWrite("_Excel_BookClose " & @error & " " & @extended & @LF)
_Excel_Close($oExcel1)
ConsoleWrite("_Excel_Close " & @error & " " & @extended & @LF)

_Excel_Open 0 1
_Excel_BookOpen 3 -2147352567
_Excel_BookSaveAs 1 0
_Excel_BookClose 1 0
_Excel_Close 0 0

But Excel is still being closed.

[water]

Sorry for not being clear in my last post.
I too can reproduce the error when the full path has not been specified for _Excel_BookOpen.
But Excel is always closed.

[JohnOne]

Must be some 64 bit issue I would imagine.

[water]

@mpower,
Could you please add the following line to your script and test?

#AutoIt3Wrapper_UseX64=y

And please test with

#AutoIt3Wrapper_UseX64=n

as well.

[mpower]

@mpower,
Could you please add the following line to your script and test?

#AutoIt3Wrapper_UseX64=y

And please test with

#AutoIt3Wrapper_UseX64=n

as well.

Tried with both, same result, Excel instance still hangs  Is there anything else I can do to help with identifying the issue?

[JohnOne]

It's clear you are taking snippets from a much larger code.

Confirm with the code from post 43 that the issue remains.

Try with and without full path top your .xlsx file.

[mpower]

It's clear you are taking snippets from a much larger code.

Confirm with the code from post 43 that the issue remains.

Try with and without full path top your .xlsx file.

Nope, even running just this causes the issue, I still reckon it has to do with a 32-bit instance of excel running on a 64-bit instance of Windows, because when I run the same few lines of codes on a machine that has both 32-bit Windows and Office or both 64-bit Windows and Office it works fine. It only seems to have a problem with the mixed 32/64-bit environment.

Here's the entire code I use for testing:

#include <Excel.au3>
$file = "C:\Blank Workbook.xlsx"
$aProcesses = ProcessList("Excel.exe")
ConsoleWrite($aProcesses[0][0] & " Excel instance(s) running." & @CRLF)
ConsoleWrite('Started Processing ' & $file & @CRLF)
$oExcel = _Excel_Open(False, False, False, False, True)
$oWorkbook = _Excel_BookOpen($oExcel, $file, True)
$aOrgArray = _Excel_RangeRead($oWorkbook)
$oWorkbook = _Excel_BookClose($oWorkbook, False)
$oExcelClose = _Excel_Close($oExcel, False, True)
Sleep(5000)
 ;just to allow some time for the process to definitely close (if it does close)
ConsoleWrite('Finished Processing ' & $file & @CRLF)
ConsoleWrite('$oExcelClose=' & $oExcelClose & '   |   @error=' & @error & '   |   @extended=' & @extended & @CRLF)
$aProcesses = ProcessList("Excel.exe")
ConsoleWrite($aProcesses[0][0] & " Excel instance(s) still running." & @CRLF)

From Win64+Office64:

0 Excel instance(s) running.
Started Processing C:\Blank Workbook.xlsx
_Excel_Open() triggered
__Excel_CloseOnQuit triggered. $bNewState=True
_Excel_BookOpen() triggered
_Excel_BookClose() triggered
_Excel_Close() triggered. $bForceClose=True
__Excel_CloseOnQuit triggered. $bNewState=Default
__Excel_CloseOnQuit triggered. $bNewState=False
Finished Processing C:\Blank Workbook.xlsx
$oExcelClose=1   |   @error=0   |   @extended=0
0 Excel instance(s) still running.

From Win64+Excel32:

0 Excel instance(s) running.
Started Processing C:\Blank Workbook.xlsx
_Excel_Open() triggered
__Excel_CloseOnQuit triggered. $bNewState=True
_Excel_BookOpen() triggered
_Excel_BookClose() triggered
_Excel_Close() triggered. $bForceClose=True
__Excel_CloseOnQuit triggered. $bNewState=Default
__Excel_CloseOnQuit triggered. $bNewState=False
Finished Processing C:\Blank Workbook.xlsx
$oExcelClose=1   |   @error=0   |   @extended=0
1 Excel instance(s) still running.

[water]

Ok. Let's see if the problem is caused by open/close or by working with a workbook.
Could you please try:

#include <Excel.au3>
$oExcel = _Excel_Open(False, False, False, False, True)
$oExcelClose = _Excel_Close($oExcel, False, True)

 

[mpower]

Ok. Let's see if the problem is caused by open/close or by working with a workbook.
Could you please try:

#include <Excel.au3>
$oExcel = _Excel_Open(False, False, False, False, True)
$oExcelClose = _Excel_Close($oExcel, False, True)

 

Hmm, this seems to work just fine! So that means the problems lies in opening the workbook.

#include <Excel.au3>
$aProcesses = ProcessList("Excel.exe")
ConsoleWrite($aProcesses[0][0] & " Excel instance(s) running." & @CRLF)
$oExcel = _Excel_Open(False, False, False, False, True)
$aProcesses = ProcessList("Excel.exe")
ConsoleWrite($aProcesses[0][0] & " Excel instance(s) running." & @CRLF)
$oExcelClose = _Excel_Close($oExcel, False, True)
Sleep(2000)
$aProcesses = ProcessList("Excel.exe")
ConsoleWrite($aProcesses[0][0] & " Excel instance(s) running." & @CRLF)

Here's the result of above test (I added so additional checks):

0 Excel instance(s) running.
_Excel_Open() triggered
__Excel_CloseOnQuit triggered. $bNewState=True
1 Excel instance(s) running.
_Excel_Close() triggered. $bForceClose=True
__Excel_CloseOnQuit triggered. $bNewState=Default
__Excel_CloseOnQuit triggered. $bNewState=False
0 Excel instance(s) running.

Edited August 2, 2015 by mpower

[water]

Fine, so lets do the next step. Use default values for Excel_Open so we get error messages in the Excel GUI:

#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()
ConsoleWrite("_Excel_Open: @error = " & @error & ", @extended = " & @extended & @CRLF)
$oWorkbook = _Excel_BookOpen($oExcel, $file, 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)
ConsoleWrite("_Excel_BookClose: @error = " & @error & ", @extended = " & @extended & @CRLF)
_Excel_Close($oExcel, False, True)
; $oExcel.Quit()
; $oExcel = 0
; $oWorkbook = 0
ConsoleWrite("_Excel_Close: @error = " & @error & ", @extended = " & @extended & @CRLF)
;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
    ConsoleWrite($i & ": " & $aProcesses[0][0] & " Excel instance(s) still running." & @CRLF)
Next

Excel seems to be closed asynchronous. When using _Excel_Close it takes about 5 loops (50 ms) for Excel to close.
BUT: When I replace _Excel_Close with the commented 3 statements it takes the full 500 loops for Excel to close (= it seems the script holds a reference to Excel so Excel isn't closed until the script ends).
Does anyone know why this happens?
EDIT: Problem solved. When _Excel_Open ist used, you need to use _Excel_Close too (see post #55).

BTW:

ConsoleWrite('$oExcelClose=' & $oExcelClose & ...

doesn't return the correct value for @error and @extended as they are reset by the preceding ConsoleWrite and Sleep statement. I changed the order of this two statements.

Edited August 3, 2015 by water

[mpower]

Fine, so lets do the next step. Use default values for Excel_Open so we get error messages in the Excel GUI:

#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()
ConsoleWrite("_Excel_Open: @error = " & @error & ", @extended = " & @extended & @CRLF)
$oWorkbook = _Excel_BookOpen($oExcel, $file, 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)
ConsoleWrite("_Excel_BookClose: @error = " & @error & ", @extended = " & @extended & @CRLF)
_Excel_Close($oExcel, False, True)
; $oExcel.Quit()
; $oExcel = 0
; $oWorkbook = 0
ConsoleWrite("_Excel_Close: @error = " & @error & ", @extended = " & @extended & @CRLF)
;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
    ConsoleWrite($i & ": " & $aProcesses[0][0] & " Excel instance(s) still running." & @CRLF)
Next

Excel seems to be closed asynchronous. When using _Excel_Close it takes about 5 loops (50 ms) for Excel to close.
BUT: When I replace _Excel_Close with the commented 3 statements it takes the full 500 loops for Excel to close (= it seems the script holds a reference to Excel so Excel isn#t closed until the script ends).
Does anyone know whyx this happens?

BTW:

ConsoleWrite('$oExcelClose=' & $oExcelClose & ...

doesn't return the correct value for @error and @extended as they are reset by the preceding ConsoleWrite and Sleep statement. I changed the order of this two statements.

Thanks for the tip around @error and @extended, I ran the code above and here's the result:

 

[water]

At least I'm now able to reproduce the problem with Excel 2010.
I will do some more testing ...

[water]

I was able to solve my problem:
When opening Excel using _Excel_Open a reference to the application object is stored in a static table.
When Excel then is NOT closed using _Excel_Close the reference still exists and is only freed when the script ends.

[water]

As you have already modified the Excel UDF, could you please add a line so we see the object was successfully overwriten in the static table?

Func __Excel_CloseOnQuit($oExcel, $bNewState = Default)
    Static $bState[101] = [0]
    If $bNewState = True Then ; Add new Excel instance to the table. Will be closed on _Excel_Close
        For $i = 1 To $bState[0]
            If Not IsObj($bState[$i]) Or $bState[$i] = $oExcel Then ; Empty cell found or instance already stored
                $bState[$i] = $oExcel
                Return True
            EndIf
        Next
        $bState[0] = $bState[0] + 1 ; No empty cell found and instance not already in table. Create a new entry at the end of the table
        $bState[$bState[0]] = $oExcel
        Return True
    Else
        For $i = 1 To $bState[0]
            If $bState[$i] = $oExcel Then ; Excel instance found
                If $bNewState = False Then ; Remove Excel instance from table (set value to zero)
                    ConsoleWrite("Excel object removed from static table" & @CRLF) ; <== ADD THIS LINE
                    $bState[$i] = 0
                    Return False
                Else
                    Return True ; Excel instance found. Will be closed on _Excel_Close
                EndIf
            EndIf
        Next
    EndIf
    Return False ; Excel instance not found. Will not be closed by _Excel_Close
EndFunc   ;==>__Excel_CloseOnQuit

 

[mpower]

As you have already modified the Excel UDF, could you please add a line so we see the object was successfully overwriten in the static table?

Func __Excel_CloseOnQuit($oExcel, $bNewState = Default)
    Static $bState[101] = [0]
    If $bNewState = True Then ; Add new Excel instance to the table. Will be closed on _Excel_Close
        For $i = 1 To $bState[0]
            If Not IsObj($bState[$i]) Or $bState[$i] = $oExcel Then ; Empty cell found or instance already stored
                $bState[$i] = $oExcel
                Return True
            EndIf
        Next
        $bState[0] = $bState[0] + 1 ; No empty cell found and instance not already in table. Create a new entry at the end of the table
        $bState[$bState[0]] = $oExcel
        Return True
    Else
        For $i = 1 To $bState[0]
            If $bState[$i] = $oExcel Then ; Excel instance found
                If $bNewState = False Then ; Remove Excel instance from table (set value to zero)
                    ConsoleWrite("Excel object removed from static table" & @CRLF) ; <== ADD THIS LINE
                    $bState[$i] = 0
                    Return False
                Else
                    Return True ; Excel instance found. Will be closed on _Excel_Close
                EndIf
            EndIf
        Next
    EndIf
    Return False ; Excel instance not found. Will not be closed by _Excel_Close
EndFunc   ;==>__Excel_CloseOnQuit

 

I was able to solve my problem:
When opening Excel using _Excel_Open a reference to the application object is stored in a static table.
When Excel then is NOT closed using _Excel_Close the reference still exists and is only freed when the script ends.

Thanks for your continuing help @water, here is the console log after adding the line:

 

Edited August 3, 2015 by mpower

[water]

I replaced all UDF functions (except RangeRead) with pure COM. So we then know if it is a problem with the UDF or somethign else.

#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)
$oWorkbook = $oExcel.Workbooks.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
$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

Could you please give it a try?

[mpower]

I replaced all UDF functions (except RangeRead) with pure COM. So we then know if it is a problem with the UDF or somethign else.

#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)
$oWorkbook = $oExcel.Workbooks.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
$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

Could you please give it a try?

Here is the result:

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_RangeRead: @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.

 

Edited August 4, 2015 by mpower
moved result to code instead of quote to reduce page scrolling

[water]

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