Page 1 of 1
COMatePlus: Close open excel objects when a program crash
Posted: Fri Sep 14, 2018 2:54 pm
by 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!
Re: COMatePlus: Close open excel objects when a program cras
Posted: Fri Sep 14, 2018 6:35 pm
by 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)...
Re: COMatePlus: Close open excel objects when a program cras
Posted: Fri Sep 14, 2018 11:01 pm
by 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.
Re: COMatePlus: Close open excel objects when a program cras
Posted: Sat Sep 15, 2018 9:04 am
by srod
excelObject\Release() does not set excelObject to zero (how could it?) - thus the infinite loop!
Re: COMatePlus: Close open excel objects when a program cras
Posted: Sat Sep 15, 2018 9:27 am
by zikitrake
srod wrote: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
Re: COMatePlus: Close open excel objects when a program cras
Posted: Sat Sep 15, 2018 10:05 am
by srod
The \Release() method is passed the value of excelObject only, not its address and so cannot set its value to 0.
Re: COMatePlus: Close open excel objects when a program cras
Posted: Sat Sep 15, 2018 12:08 pm
by zikitrake
srod wrote: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?