Excel Automnation

[Progress_Guy]

Hi folks,

We do alot of reports to Excel and find we are able to use it to a quite advanced level.

We tend to :-

• Open the Excel session
• Keep it hidden in the background
• Populate (or read it)
• Allow user to save it, print, etc. - by sending relevant commands from Progress
• Close it and tidy up all handles (release and delete them).

Frequently the user isn't even aware that he/she is using Excel, because they may simply get a Progress dialog popping up asking which printer to send the 'report' to.

However, I have a scenario where I want to make it visible (which I can do) and allow the user to populate it (which I can also do) and allow him/her to use standard Excel to save, print and close it.

However, when the user closes it (by physically closing Excel), it still leaves a dead session in the background. If I release and delete my objects then the Excel session closes down straight away before the user can manipulate it.

If I don't clear the handles up then the dead session remains !!

Here is my code for tidying up. Is there any of this that I can miss out (or additional command to add) that'll leave an Excel session, but, be disjoint from Progress.

(Usually I also have code to close down sheets and quit Excel).

RELEASE OBJECT chactiveworksheet NO-ERROR. /* Active Sheet */
RELEASE OBJECT chactiveworkbook NO-ERROR. /* WorkBook */
RELEASE OBJECT chExcelApplication NO-ERROR. /* Excel itself */

Many Thanks in advance.

 

[Lee Curzon]

Hi Dave,

Your tidy-up code looks correct to me - assuming these are the only Excel objects left to be released.

Perhaps the problem is in your Open Excel code - are you trying to connect to any existing instance of Excel before you create a new object? ie. use the CONNECT option on the CREATE statement.

Check (in task manager) how many Excel instances are open before the user closes Excel (before and after closing Progress), obviously there should be one only. If there is only one then your open code is probably correct.

This leaves your Excel manipulation code - unfortunately these bugs can be obscure, as I'm sure you know. The likelihood in this case is that you are 'missing' a release somewhere, or your bug is one of the two or three dealt with in the Knowledgebase.

Lee

 

[it@flude.co.uk]

Have you tried writing the spreadsheet, releasing all the objects, closing Excel, to get rid of the zombie process, then opening it up again just by launching the spreadsheet?

Unless you want to do things in Progress dependant on what the user is doing in the spreadsheet, there is no need to continue using the same Excel process as you started off with.

That's what I normally do when they need to check/modify the generated Excel Spreadsheet.

You don't even need to use API calls to launch the Excel Spreadssheet. You call call Excel explicitly, use ShellExecute or something else.

Simon Phipp

 

[gcampbell]

Or go to www.global-shared.com and find the Windows API code to determine how to find out if a Process is still running and if so, look at the code to determine how to kill the Process.

Here is an example that looks for an Adobe Process and kills it.

www.epro-sys.com/samples/killadobe.p

Later,
Gordon