COMatePlus: Close open excel objects when a program crash

[zikitrake]

Hi! If I execute this code and it crash before ;CLOSE WORKBOOK or ;CLOSE EXCEL AND FREE THE OBJECT the object is kept in memory.

How can I close previous objects before running this program?
(If possible, of course)

Code: Select all

Protected ExcelObject.COMateObject,MyWorkbook
Protected Workbook.COMateObject

;EXCEL OBJECT CREATION
ExcelObject = COMate_CreateObject("Excel.Application")
If ExcelObject
  MyWorkBook = ExcelObject\GetObjectProperty("Workbooks\Add")    
EndIf

;SET IT INVISIBLE
ExcelObject\SetProperty("Visible = #False")

;HERE, OTHER PUREBASIC PROCEDURES

;WRITE A TEXT IN A CELL
ExcelObject\SetProperty("Cells("+ Str(1) + "," + Str(1) + ")\Value = '" + "I'm a text in a cell" + "'")

;OTHER PUREBASIC PROCEDURES

;SAVE WORKBOOK
Workbook=ExcelObject\GetObjectProperty("ActiveWorkbook")
If FileName <> ""
  If Workbook
     Workbook\Invoke("SaveAs('"+ "myExcelFile.csv"+"', " + Str(6) + ")")
     Workbook\Release()
  EndIf
EndIf
     
;CLOSE WORKBOOK
Workbook=ExcelObject\GetObjectProperty("ActiveWorkbook")
If Workbook
  Workbook\Invoke("Close")
  Workbook\Release()
EndIf

;CLOSE EXCEL AND FREE THE OBJECT 
ExcelObject\Invoke("Quit()")
ExcelObject\Release()

Thank you in advance!

[spikey]

By definition you've lost control somewhat so there aren't going to be any perfect solutions, but you can build in some robustness to try and regain some control.

This will use/reuse a running instance of the application.

Code: Select all

;EXCEL OBJECT CREATION

; Try to get a link to a running instance.
ExcelObject = COMate_GetObject("", "Excel.Application")

; If this fails create a new instance.  
If Not ExcelObject
  ExcelObject = COMate_CreateObject("Excel.Application")
EndIf

If ExcelObject
  MyWorkBook = ExcelObject\GetObjectProperty("Workbooks\Add")   
EndIf

The workbook is more problematic because it has two distinct phases - unnamed file and named/saved file.
The easiest solution would be to save it as soon as you create it, before adding any content.
That way, in the event of a crash and the code above reusing the running application, you can enumerate the open workbooks through the Workbooks collection and see if the filename is one you want to reuse (or discard)...

[zikitrake]

Thank you for your solution! Could you tell me why the following code goes into an infinite loop (it doesn't seem to close excel sessions as ExcelObject = COMate_GetObject("", "Excel.Application") always returns a value other than 0)

Code: Select all

Define ExcelCount.l = 0
Define visible
Define ExcelObject

Repeat

  ExcelObject = 0
  ExcelObject = COMate_GetObject("", "Excel.Application")
  
  If ExcelObject    

    ExcelCount+1

    ;WorkBook = ExcelObject\GetObjectProperty("Workbooks\Add"
  
    ExcelObject\SetProperty("Application\DisplayAlerts = #False")

    visible = ExcelObject\GetIntegerProperty("Visible")
    
    Debug "ExcelCount: " + Str(ExcelCount)
    Debug "ExcelObject: " + Str(ExcelObject)
    Debug "visible: " + Str(visible)
    
    ExcelObject\Invoke("Quit()")
    excelObject\Release()

  EndIf
  
Until Not ExcelObject
 
End

PS: I don't know if I missed some of your explanation, my English is limited and I often don't get 100% of your comments.

[srod]

excelObject\Release() does not set excelObject to zero (how could it?) - thus the infinite loop!

[zikitrake]

excelObject\Release() does not set excelObject to zero (how could it?) - thus the infinite loop!

Sorry for the silly question then, I thought the excelObject\Release() option would do it

[srod]

The \Release() method is passed the value of excelObject only, not its address and so cannot set its value to 0.

[zikitrake]

The \Release() method is passed the value of excelObject only, not its address and so cannot set its value to 0.

I understand. So, my best option is to get a list of the processes that have been blocked excel and kill them?