AutoIt COM (Excel Still Resides In Memory)

[bshoenhair]

After you Loose the Excel Object, Excel it still resides in Memory until the script is terminated. This is a known issue but is there Any Fix or WorkArounds without having to terminate the script ?

Similar Documented Issue

Example:

expandcollapse popup

; Excel Automation Example
;
; Using direct assigments of 2-dimensional array's
;
; Based on AutoItCOM version 3.1.0
;
; Beta version 06-02-2005

While ProcessExists('excel.exe')
    MsgBox(0,"Close Excel", "Close all instances of Excel before continuing example !")
    Sleep(1)
WEnd

$MyExcel = ObjCreate("Excel.Application"); Create an Excel Object

if @error then 
  Msgbox (0,"","Error creating Excel object. Error code: " & @error)
  exit
endif

if not IsObj($MyExcel) then 
  Msgbox (0,"ExcelTest","I'm sorry, but creation of an Excel object failed.")
  exit
endif


$MyExcel.Visible = 1    ; Let the guy show himself

$MyExcel.workbooks.add  ; Add a new workbook

            ; Example: Fast Fill some cells

Msgbox (0,"","Click 'ok' to fastfill some cells")

dim $arr[16][16]

  for $i = 1 to 15
    for $j = 1 to 15
    $arr[$i][$j] = $i
    next
 next   

; Set all values in one shot!
$MyExcel.activesheet.range("A1:N15").value = $arr

 
Msgbox (0,"","Click 'ok' to clear the cells")

$MyExcel.activesheet.range("A1:N15").clear

Sleep(2000)

$MyExcel.activeworkbook.saved = 1; To prevent 'yes/no' questions from Excel

$MyExcel.quit           ; Get rid of him.

Msgbox (0,"ExcelTest","Is Excel gone now ?")
                ; Nope, only invisible, 
                ; but should be still in memory.

$MyExcel = 0    ; Loose this object.
                ; Object will also be automatically discarded when you exit the script

While ProcessExists('excel.exe')
    MsgBox(0,"Still In Memory", "The Object was discarded and Excel is not visible but Excel still resides in memory and will remain so until this script is closed.")
    Sleep(1)
WEnd

[randallc]

Don't really know; I'm interested, though!

If its a real problem, your reference article says vbscript works; perhaps you can shell out to a vbs script to "Getobject" excel (not always so easy!), and quits it?

Interested to hear if you find an answer,

Randall

[bshoenhair]

@Sven

When the script closes it releases Excel from memory.

Is there any way to release Excel with out closing the script ?

[Valik]

Speaking on Sven's behalf, this is currently a bug. There are oustanding object references hanging around which can cause problems. This is the product of alpha software (Which is what I would consider the COM code at the moment).

As a work-around, can you re-run the script using a command-line paramter which sends it into the COM/Excel code. In this way, that child script will use COM/Excel and when its done, terminate, thus freeing the resources. In this way, the parent script can continue but the COM objects are properly released and so Excel should close. I'm sure this isn't going to be very pretty or very elegant, however, if the lingering Excel process is bothering you/causing problems, this is the only AutoIt-only work-around I can think of that is safe (ProcessClose()ing Excel probably isn't safe for a variety of reasons).

I will emphasize, though, that this is a bug. It will be fixed someday. COM is just a very difficult and finicky thing, especially to debug.

[w0uter]

this is a possible work around

expandcollapse popup

$Para = $CmdLineRaw & ''
if StringInStr($Para, 'therest') Then _dorest()

$MyExcel = ObjCreate("Excel.Application"); Create an Excel Object

if @error then 
  Msgbox (0,"","Error creating Excel object. Error code: " & @error)
  exit
endif

if not IsObj($MyExcel) then 
  Msgbox (0,"ExcelTest","I'm sorry, but creation of an Excel object failed.")
  exit
endif


$MyExcel.Visible = 1   ; Let the guy show himself

$MyExcel.workbooks.add   ; Add a new workbook

           ; Example: Fast Fill some cells

Msgbox (0,"","Click 'ok' to fastfill some cells")

dim $arr[16][16]

  for $i = 1 to 15
    for $j = 1 to 15
    $arr[$i][$j] = $i
    next
next   

; Set all values in one shot!
$MyExcel.activesheet.range("A1:N15").value = $arr


Msgbox (0,"","Click 'ok' to clear the cells")

$MyExcel.activesheet.range("A1:N15").clear

Sleep(2000)

$MyExcel.activeworkbook.saved = 1; To prevent 'yes/no' questions from Excel

$MyExcel.quit          ; Get rid of him.
Run(@ComSpec & " /c " & @ScriptFullPath & ' therest', "", @SW_HIDE)
Exit

Func _dorest()

Msgbox(0, '', 'excel should be gone from the memory now')
Exit

EndFunc

[bshoenhair]

OK, thanks the the responces

I have a work around that allows you to keep your script open and removes Excel from memory.

expandcollapse popup

While 1
    $BeforeList = _GetProcessList()
    $MyExcel = ObjCreate("Excel.Application"); Create an Excel Object
    $AfterList = _GetProcessList()
    if @error then
        Msgbox (0,"","Error creating Excel object. Error code: " & @error)
        exit
    endif
    if not IsObj($MyExcel) then
        Msgbox (0,"ExcelTest","I'm sorry, but creation of an Excel object failed.")
        exit
    endif
    $MyExcel.Visible = 1    ; Let the guy show himself
    $MyExcel.workbooks.add  ; Add a new workbook
   ; Example: Fast Fill some cells
    Msgbox (0,"","Click 'ok' to fastfill some cells")
    dim $arr[16][16]
    
    for $i = 1 to 15
        for $j = 1 to 15
            $arr[$i][$j] = $i
        next
    next
   ; Set all values in one shot!
    $MyExcel.activesheet.range("A1:N15").value = $arr
    Msgbox (0,"","Click 'ok' to clear the cells")
    $MyExcel.activesheet.range("A1:N15").clear
    Sleep(500)
    $MyExcel.activeworkbook.saved = 1; To prevent 'yes/no' questions from Excel
    $MyExcel.quit           ; Get rid of him.
    $MyExcel = 0            ; Get rid of Object
    Msgbox (0,"ExcelTest","Is Excel gone now ?" & @CRLF & "No its invisible but still resides in memory !")
   ; Nope, only invisible,
   ; but should be still in memory.
    
   ;Remove from memory
    $Compare = StringSplit($AfterList,@CRLF,1)
    For $i = 1 To $Compare[0]
        if Not StringInStr($BeforeList,$Compare[$i]) Then ProcessClose($Compare[$i])
    Next
    Msgbox (0,"ExcelTest","Is Excel gone now ?" & @CRLF & "Yes, now it is gone.")
Wend

Func _GetProcessList()
    $var = ProcessList('excel.exe')
    $Pids = ''
    For $i = 1 to $var[0][0]
        $Pids = $Pids &  $var[$i][1] & @CRLF
    Next
    $Pids = StringStripWS($Pids,3)
    Return $Pids
EndFunc  ;==>_GetProcessList

[SvenP]

Hello,

I fixed this nasty bug in the COM code today. I hope jpm has the time to release the new (pre-beta!) version soon.

Thanks for letting me know about this bug (and ofcourse Valik, who complained first ;-).

Regards,

-Sven

[jpm]

Hello,

I fixed this nasty bug in the COM code today.  I hope jpm has the time to release the new (pre-beta!) version soon.

Thanks for letting me know about this bug (and ofcourse Valik, who complained first ;-).

Regards,

-Sven

<{POST_SNAPBACK}>

I merge your code in 3.1.1.3