It is currently Sat Sep 22, 2018 12:58 am

All times are UTC + 1 hour




Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: COMatePlus: Close open excel objects when a program crash
PostPosted: Fri Sep 14, 2018 2:54 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Thu Mar 25, 2004 2:15 pm
Posts: 606
Location: Spain
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:
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! :)

_________________
PB 5.6x, PureVision and EsGrid User.


Top
 Profile  
Reply with quote  
 Post subject: Re: COMatePlus: Close open excel objects when a program cras
PostPosted: Fri Sep 14, 2018 6:35 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Wed Sep 22, 2010 1:17 pm
Posts: 262
Location: United Kingdom
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:
;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)...


Top
 Profile  
Reply with quote  
 Post subject: Re: COMatePlus: Close open excel objects when a program cras
PostPosted: Fri Sep 14, 2018 11:01 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Thu Mar 25, 2004 2:15 pm
Posts: 606
Location: Spain
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:
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.

_________________
PB 5.6x, PureVision and EsGrid User.


Top
 Profile  
Reply with quote  
 Post subject: Re: COMatePlus: Close open excel objects when a program cras
PostPosted: Sat Sep 15, 2018 9:04 am 
Offline
PureBasic Expert
PureBasic Expert

Joined: Wed Oct 29, 2003 4:35 pm
Posts: 10440
Location: Beyond the pale...
excelObject\Release() does not set excelObject to zero (how could it?) - thus the infinite loop!

_________________
I may look like a mule, but I'm not a complete ass.


Top
 Profile  
Reply with quote  
 Post subject: Re: COMatePlus: Close open excel objects when a program cras
PostPosted: Sat Sep 15, 2018 9:27 am 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Thu Mar 25, 2004 2:15 pm
Posts: 606
Location: Spain
srod wrote:
excelObject\Release() does not set excelObject to zero (how could it?) - thus the infinite loop!


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

_________________
PB 5.6x, PureVision and EsGrid User.


Top
 Profile  
Reply with quote  
 Post subject: Re: COMatePlus: Close open excel objects when a program cras
PostPosted: Sat Sep 15, 2018 10:05 am 
Offline
PureBasic Expert
PureBasic Expert

Joined: Wed Oct 29, 2003 4:35 pm
Posts: 10440
Location: Beyond the pale...
The \Release() method is passed the value of excelObject only, not its address and so cannot set its value to 0.

_________________
I may look like a mule, but I'm not a complete ass.


Top
 Profile  
Reply with quote  
 Post subject: Re: COMatePlus: Close open excel objects when a program cras
PostPosted: Sat Sep 15, 2018 12:08 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Thu Mar 25, 2004 2:15 pm
Posts: 606
Location: Spain
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?

_________________
PB 5.6x, PureVision and EsGrid User.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 13 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye