Excel Handle left hanging around

L

Lesley MacMahon

New Member
Hi,

I have inherited a Progress GUI application that produces reports in Excel. There are several reports that all work fine because they produce the information in a single worksheet. My report has to take a new worksheet on change of Project. I have this working fine except that when the procedure finishes and I check the Task Manager Excel is still there. The user will want to rerun the procedure within the same session so more and more Excel processes are left hanging around. Can anyone see what I am missing from the following code:-

DEFINE VARIABLE chXL AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chXLBook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chXLSht AS COM-HANDLE NO-UNDO.

DEFINE VARIABLE vReportName AS CHARACTER INITIAL "TestExcelReport" NO-UNDO.
DEFINE VARIABLE vName AS CHARACTER NO-UNDO.
DEFINE VARIABLE i AS INTEGER NO-UNDO.

CREATE "Excel.Application" chXL.
ASSIGN chXL:VISIBLE = true.

/* open Project Detail spreadsheet template */
chXL:Workbooks:OPEN("f:\gpr\templates\Project Detail.xls").
ASSIGN chXLBook = chXL:WorkBooks:ITEM(1).

DO i = 1 TO 5:
chXLBook:worksheets(1):COPY(,chXLBook:worksheets(i)).

/* select the new sheet */
chXLSht = chXLBook:worksheets(i).
ASSIGN vName = "TestSheet " + STRING(i).
chXLSht:Range("A20"):VALUE = vName.

/* give worksheet the project name */
chXLSht:NAME = "Page " + STRING(i).
END.

chXLBook:SaveAs("f:\gpr\" + vReportName + ".xls",43,,,,,).

chXL:Application:Workbooks:CLOSE() NO-ERROR.
chXL:Application:QUIT NO-ERROR.

RELEASE OBJECT chXLSht.
RELEASE OBJECT chXLBook.
RELEASE OBJECT chXL.

ASSIGN chXLSht = ?.
ASSIGN chXLBook = ?.
ASSIGN chXL = ?.

I want to use an existing Excel spreadSheet as a template because it sets up the Headings of the report.

Thanks for any help you can give,
Lesley MacMahon
 
Hi,

to avoid this behavior you have to release all the handles before closing excel. In your case, just move theses statments:

RELEASE OBJECT chXLSht.
ASSIGN chXLSht = ?.

INSIDE the do i = 1 ... loop like that
/* original code */ chXLSht:NAME = "Page " + STRING(i).

RELEASE OBJECT chXLSht.
ASSIGN chXLSht = ?.

/* original code */ END.

In this case excel will end at the end of the procedure.

The brief explanation is:
- first loop (i=1): chxlsht refers to the second sheets
- second (and other) loops: chxlsht refets to second (or other loops)
- outside the loop chxlst still refers to sheets(6), so the release object only release this sheet but the links between Progress and
the others sheets already exist.

This is why excel won't close. In your example it seems you don't have to store more than one sheet at a time, you can release it just after you use it...

Oooops, my (brief) explanation is growing and my poor english ask me too much effort to continue. Try to use the search engine about "close excel" on this forum you will be surprised by the quality of the answers.

Stéphane.

Lesley MacMahon said:
Hi,

I have inherited a Progress GUI application that produces reports in Excel. There are several reports that all work fine because they produce the information in a single worksheet. My report has to take a new worksheet on change of Project. I have this working fine except that when the procedure finishes and I check the Task Manager Excel is still there. The user will want to rerun the procedure within the same session so more and more Excel processes are left hanging around. Can anyone see what I am missing from the following code:-

DEFINE VARIABLE chXL AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chXLBook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chXLSht AS COM-HANDLE NO-UNDO.

DEFINE VARIABLE vReportName AS CHARACTER INITIAL "TestExcelReport" NO-UNDO.
DEFINE VARIABLE vName AS CHARACTER NO-UNDO.
DEFINE VARIABLE i AS INTEGER NO-UNDO.

CREATE "Excel.Application" chXL.
ASSIGN chXL:VISIBLE = true.

/* open Project Detail spreadsheet template */
chXL:Workbooks:OPEN("f:\gpr\templates\Project Detail.xls").
ASSIGN chXLBook = chXL:WorkBooks:ITEM(1).

DO i = 1 TO 5:
chXLBook:worksheets(1):COPY(,chXLBook:worksheets(i)).

/* select the new sheet */
chXLSht = chXLBook:worksheets(i).
ASSIGN vName = "TestSheet " + STRING(i).
chXLSht:Range("A20"):VALUE = vName.

/* give worksheet the project name */
chXLSht:NAME = "Page " + STRING(i).
END.

chXLBook:SaveAs("f:\gpr\" + vReportName + ".xls",43,,,,,).

chXL:Application:Workbooks:CLOSE() NO-ERROR.
chXL:Application:QUIT NO-ERROR.

RELEASE OBJECT chXLSht.
RELEASE OBJECT chXLBook.
RELEASE OBJECT chXL.

ASSIGN chXLSht = ?.
ASSIGN chXLBook = ?.
ASSIGN chXL = ?.

I want to use an existing Excel spreadSheet as a template because it sets up the Headings of the report.

Thanks for any help you can give,
Lesley MacMahon
Click to expand...
 
Hi Stéphane,

Thanks for your reply. I moved the two lines :-

RELEASE OBJECT chXLSht.
ASSIGN chXLSht = ?.

inside the loop but the Excel handle is still left behind. Is this what you were advising me to do? The following is the code now.


DEFINE VARIABLE chXL AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chXLBook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chXLSht AS COM-HANDLE NO-UNDO.

DEFINE VARIABLE vReportName AS CHARACTER INITIAL "TestExcelReport" NO-UNDO.
DEFINE VARIABLE vName AS CHARACTER NO-UNDO.
DEFINE VARIABLE i AS INTEGER NO-UNDO.

CREATE "Excel.Application" chXL.
ASSIGN chXL:VISIBLE = true.

/* open Project Detail spreadsheet template */
chXL:Workbooks:OPEN("f:\gpr\templates\Project Detail.xls").
ASSIGN chXLBook = chXL:WorkBooks:ITEM(1).

DO i = 1 TO 5:
chXLBook:worksheets(1):COPY(,chXLBook:worksheets(i)).

/* select the new sheet */
chXLSht = chXLBook:worksheets(i).
ASSIGN vName = "TestSheet " + STRING(i).
chXLSht:Range("A20"):VALUE = vName.

/* give worksheet the project name */
chXLSht:NAME = "Page " + STRING(i).
RELEASE OBJECT chXLSht.
ASSIGN chXLSht = ?.
END.

chXLBook:SaveAs("f:\gpr\" + vReportName + ".xls",43,,,,,).

chXL:Application:Workbooks:CLOSE() NO-ERROR.
chXL:Application:QUIT NO-ERROR.

/* RELEASE OBJECT chXLSht. */
RELEASE OBJECT chXLBook.
RELEASE OBJECT chXL.

/* ASSIGN chXLSht = ?. */
ASSIGN chXLBook = ?.
ASSIGN chXL = ?.


Regards,
Lesley
 
I see,

try invert theses lines :
/* original code */
chXLBook:worksheets(1):COPY(,chXLBook:worksheets(i)).

/* select the new sheet */
chXLSht = chXLBook:worksheets(i).

in this way
/* select the new sheet */
chXLSht = chXLBook:worksheets(i).

chXLBook:worksheets(1):COPY(,chXLBook:worksheets(i)).

I've done it for my own test and it seems to work... But please, don't ask me why because i don't know...

Regards.

Stéphane.

Lesley MacMahon said:
Hi Stéphane,

Thanks for your reply. I moved the two lines :-

RELEASE OBJECT chXLSht.
ASSIGN chXLSht = ?.

inside the loop but the Excel handle is still left behind. Is this what you were advising me to do? The following is the code now.


DEFINE VARIABLE chXL AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chXLBook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chXLSht AS COM-HANDLE NO-UNDO.

DEFINE VARIABLE vReportName AS CHARACTER INITIAL "TestExcelReport" NO-UNDO.
DEFINE VARIABLE vName AS CHARACTER NO-UNDO.
DEFINE VARIABLE i AS INTEGER NO-UNDO.

CREATE "Excel.Application" chXL.
ASSIGN chXL:VISIBLE = true.

/* open Project Detail spreadsheet template */
chXL:Workbooks:OPEN("f:\gpr\templates\Project Detail.xls").
ASSIGN chXLBook = chXL:WorkBooks:ITEM(1).

DO i = 1 TO 5:
chXLBook:worksheets(1):COPY(,chXLBook:worksheets(i)).

/* select the new sheet */
chXLSht = chXLBook:worksheets(i).
ASSIGN vName = "TestSheet " + STRING(i).
chXLSht:Range("A20"):VALUE = vName.

/* give worksheet the project name */
chXLSht:NAME = "Page " + STRING(i).
RELEASE OBJECT chXLSht.
ASSIGN chXLSht = ?.
END.

chXLBook:SaveAs("f:\gpr\" + vReportName + ".xls",43,,,,,).

chXL:Application:Workbooks:CLOSE() NO-ERROR.
chXL:Application:QUIT NO-ERROR.

/* RELEASE OBJECT chXLSht. */
RELEASE OBJECT chXLBook.
RELEASE OBJECT chXL.

/* ASSIGN chXLSht = ?. */
ASSIGN chXLBook = ?.
ASSIGN chXL = ?.


Regards,
Lesley
Click to expand...
 
Hi Stéphane,

Thanks, that works but I don't understand why it does either.
Thats great I can tell my boss the good news and put my report into production.

Lesley
 
You must log in or register to reply here.
Back
Top