subtleties of _Excel_Open

[photonblaster]

I am trying to make sure I completely understand how _Excel_Open works.  The help file says "Connects to an existing Excel instance or creates a new one".  So I would expect the following sequence to result in two open Excel, and $oExcel3=$oExcel1 (or maybe $oExcel2)

Local $oExcel1 = ObjCreate("Excel.Application")
Local $oExcel2 = _Excel_Open(Default, Default, Default, Default, True)
Local $oExcel3 = _Excel_Open()

Using the example from the help file as a starting point, I added the third opening of Excel.  And some info displays.  Now, when code is completed, there is still one Excel object open.  If you comment out the lines for the second _Excel_Open (29-36) it reverts to the help file results, opening and then closing two instances of Excel.  If I use the code below but change line 40 to close $oExcel3, NOTHING gets closed, not even $oExcel1 which is supposed to be forced close. Similar results if get rid of the $oExcel2 open line....$oExcel1 does not close.

I either messed up my script and/or I really do not understand what is going on.  Can you Please give me some guidance here...why do I not get what I expected?

PS  This is not totally random question... I came across this when I was trying to close all  Excel previously opened prior to running the script...using Process list to see if there are instances of  Excel open, and trying to use _Excel_Open to the get  all the $oExcel so I can close them with _Excel_Close.  I suspect after responses here I will eventually have to ask for help with that.

 

expandcollapse popup

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object
Local $oExcel1 = ObjCreate("Excel.Application")
If @error Then
    Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example", "Error creating the first Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Else
    Local $aProcesses = ProcessList("Excel.exe")
    $PID1 = $aProcesses[1][1]
    ToolTip("Fist PID= " & $PID1)
    _ArrayDisplay($aProcesses)
    ToolTip("")
EndIf

; Open Excel application (force new instance)
Local $oExcel2 = _Excel_Open(Default, Default, Default, Default, True)
If @error Then
    Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example", "Error creating the second Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Else
    Local $aProcesses = ProcessList("Excel.exe")
    $PID2 = $aProcesses[2][1]
    ToolTip("Second Open Excel, PID's are: " & $PID1 & ", " & $PID2)
    _ArrayDisplay($aProcesses)
    ToolTip("")
EndIf

;######  attach to one of open Excel (at least I think that is what is supposed to happen)
Local $oExcel3 = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example", "Error creating the second Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
If $oExcel3 = 0 Then MsgBox($MB_SYSTEMMODAL, "Third Excel Open", "did not get object assigned")

ToolTip("Third Excel_Open but should be two Excel, PID= " & $PID1 & ", " & $PID2)
Local $aProcesses = ProcessList("Excel.exe")
_ArrayDisplay($aProcesses)
ToolTip("")


; Close the Excel instance opened by _Excel_Open
_Excel_Close($oExcel2)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example 2", "Error closing the Excel application." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Sleep(2000)
$aProcesses = ProcessList("Excel.exe")
;MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example 2", "Function ended successfully." & @CRLF & @CRLF & $aProcesses[0][0] & " Excel instance(s) still running.")
ToolTip("Should be only the first Excel PID= " & $PID1)
$aProcesses = ProcessList("Excel.exe")
_ArrayDisplay($aProcesses)
ToolTip("")

; Force the Excel instance not opened by _Excel_Open
; without saving open workbooks
_Excel_Close($oExcel1, Default, True)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example 3", "Error closing the Excel application." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Sleep(2000)
ToolTip("Supposed to be zero open Excel")
$aProcesses = ProcessList("Excel.exe")
;MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example 3", "Function ended successfully." & @CRLF & @CRLF & $aProcesses[0][0] & " Excel instance(s) still running.")

$aProcesses = ProcessList("Excel.exe")
_ArrayDisplay($aProcesses)

 

[junkew]

weird I do not get what I would expect on 64 bit windows.

I would have expected to keep excel open after script terminates instead now excel closes/terminates.

When I open/add a workbook behaviour is like expected that it stays open.

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $oExcel1 = ObjCreate("Excel.Application")
$oExcel1.visible=True
;$oExcel1.workbooks.add()
;Local $oExcel2 = _Excel_Open(Default, Default, Default, Default, True)
;Local $oExcel3 = _Excel_Open()

sleep(5000)

 

[water]

Even when you do not close Excel explicitely AutoIt closes all Excel instances created during runtime when doing is cleanup.
That's why you still see an Excel instance in the process list after the last _Excel_Close call but you don't see an instance when the AutoIt script has ended.

Seems there is still a reference to the Excel application.

[water]

Youn need to release the references to the Excel applications held in $oExcel1, $oExcel2 and $oExcel3.

; Force the Excel instance not opened by _Excel_Open without saving open workbooks
_Excel_Close($oExcel1, Default, True)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example 3", "Error closing the Excel application." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Release all references to the Excel application objects
$oExcel1 = 0
$oExcel2 = 0
$oExcel3 = 0
Sleep(2000)
ToolTip("Supposed to be zero open Excel")
$aProcesses = ProcessList("Excel.exe")
_ArrayDisplay($aProcesses)

 

[water]

Better solution.
You now close Excel 2 and Excel 3 but the reference to Excel 1 still exists till the script ends.

The problem is caused by you looking at the number of process (2) and not on the number of Excel instances (3).

Close both references to Excel1.

; Force the Excel instance not opened by _Excel_Open without saving open workbooks
_Excel_Close($oExcel1, Default, True)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error closing Excel1 application." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oExcel3, Default, True)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error closing Excel2 application." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

 

[photonblaster]

Thanks for the all the feedback...these are subtleties that are hard to find for AutoIt novices .

[water]