Highlighted
Absent Member.
Absent Member.
3181 views

Closing Excel application after Creating

[Migrated content. Thread originally posted on 28 February 2011]

I am creating an Excel application from a called .Acu program using:-

CREATE Application OF Excel HANDLE IN hExcelApp

I then do various inquires, modifies etc, before saving the output .xls file and exiting.
All this works with no problems, but after closing the application, Task Manager still shows Excel as being present as a process. If I run the program multiple times, I end up with Task manager showing multiple occurrences of Excel present as processes. How do I properly close Excel, once I have finished with the application?
Coding in the program at present is as follows:-


MODIFY hExcelApp @Quit()
destroy hExcelApp

Exit Program.
0 Likes
11 Replies
Highlighted
Absent Member.
Absent Member.

RE: Closing Excel application after Creating

COM maintains the number of connections to a process. If the number of connections are not zero, a process will stay alive.

So, what you experience is that you have one or more connections to your instance of Excel still running.

Here is a small example that launch Excel and close it, destroying the connections (handles) and excel goes away.

       IDENTIFICATION DIVISION.
       PROGRAM-ID. ExcelHelloWorld.
       ENVIRONMENT DIVISION.
       CONFIGURATION                SECTION.
       SPECIAL-NAMES.
           COPY    "MSEXCEL.def".
                   .
       DATA        DIVISION.
       WORKING-STORAGE              SECTION.
       77  olExcel                  HANDLE OF APPLICATION.
       77  olWrkBk                  HANDLE OF WORKBOOK.
       
       PROCEDURE DIVISION.
       Main.
           CREATE  Application      OF Excel
                   HANDLE           IN olExcel.
           MODIFY  olExcel          @Visible = 1.
           MODIFY  olExcel          Workbooks::Add()
                   GIVING           olWrkBk.
           MODIFY  olWrkBk          @Close(BY NAME SaveChanges 0).
           DESTROY olWrkBk.
           MODIFY  olExcel          Quit().
           DESTROY olExcel.
           GOBACK.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: Closing Excel application after Creating

Thanks for the reply, but all handles are destroyed, sorry, I only posted code I thought was significant. I am obviously missing something, as I compiled and ran you example program, and as you say, Excel goes away, yet with my program Excel remains as a process, even though, as far as I can see I have got rid of all connections. Here is the full code of my test program, dont worry about all the inquires and modifies, but if you could see where I am leaving some connection open, I would be grateful.

IDENTIFICATION DIVISION.
       PROGRAM-ID. excel-test.
     
       DATE-WRITTEN.                    2011/02/25 - 09:06:00.
       DATE-COMPILED.                   2011/02/25 - 09:10:00.

      ******************************************************************
       ENVIRONMENT DIVISION.
       CONFIGURATION SECTION.
       SPECIAL-NAMES.
       COPY "excel.def".
           .
       INPUT-OUTPUT SECTION.
       FILE-CONTROL.
     
       DATA DIVISION.
       FILE SECTION.
     
       WORKING-STORAGE SECTION.

       01 EXCEL-OBJECTS.
           03 hExcelApp  HANDLE OF APPLICATION OF EXCEL.
           03 hExcelWkb  HANDLE OF WORKBOOK    OF EXCEL.
           03 hExcelWkss HANDLE OF WORKSHEETS  OF EXCEL.
           03 hExcelWks  HANDLE OF WORKSHEET   OF EXCEL.
           03 hRange     HANDLE OF RANGE       OF EXCEL.
           03 hFont      HANDLE OF @FONT       OF EXCEL.
           03 hInterior  HANDLE OF @INTERIOR   OF EXCEL.
           03 hBorders   HANDLE OF @BORDERS    OF EXCEL.
       01  other-work.
           02  sub  value 0           pic 9(04).
           02  wht-range.
               04  wht-col1           pic x.
               04  wht-row1           pic x(03).
               04  filler value ':'   pic x.
               04  wht-col2           pic x.
               04  wht-row2           pic x(03).

      *LINKAGE SECTION.
       01  csv-link-area.
           02 lk-file-type value 'd'                       pic x.
              88 moneynodata value 'm'.
              88 datanomoney value 'd'.
           02 lk-worksheet-name value 'money no data'      pic x(13).
           02 lk-csv-in-file                               pic x(23)
              value 'c:\test\datanomoney.csv'.
           02 lk-xls-out-file                              pic x(23)
              value 'c:\test\datanomoney.xls'.
           02 test-tab                                     pic x(36)
           value 'N001N002N003N004T005T006N007T008N009'.
           02  highlight-table redefines test-tab.
               04  ht-entry occurs 9.
                   06 ht-type         pic x.
                   06 ht-row          pic x(03).

             
       SCREEN SECTION.
       PROCEDURE DIVISION.
       Main Section.
     
           CREATE Application OF Excel HANDLE IN hExcelApp
       
      *    Turn Excel visible. Customer can then interact.
            modify hExcelApp @Visible = 1

     
           modify hExcelApp @Workbooks::Open
            lk-csv-in-file giving hExcelWkb

     
           INQUIRE hExcelWkb Worksheets = hExcelWkss.
     
         
           INQUIRE hExcelWkss Item(1) = hExcelWks.
           modify hExcelWks Name = lk-worksheet-name.

     


             move 'A' to wht-col1
             move 'F' to wht-col2
             perform varying sub from 1 by 1 until ht-type (sub) = space
               or sub > 998
               move ht-row (sub) to wht-row1
               move ht-row (sub) to wht-row2
               inquire hExcelWks Range(wht-range) hRange
               inquire hRange @Interior hInterior
               if ht-type (sub) = 'N'
                  modify hInterior @color = "255"
                else
                  modify hInterior @Color = "65535"
               end-if
               inquire hExcelWks Range(wht-range) hRange
               inquire hRange @borders hBorders
               modify hBorders(xlEdgeLeft)
               @linestyle = (xlContinuous)
               modify hBorders(xlEdgeTop)
               @linestyle = (xlContinuous)
               modify hBorders(xlEdgeRight)
               @linestyle = (xlContinuous)
               modify hBorders(xlEdgeBottom)
               @linestyle = (xlContinuous)
           end-perform
            inquire hExcelWks Range("A1:L1")hRange
            inquire hRange @Font hFont
            modify hFont @Bold = "True"
            modify hFont @Underline = (xlUnderlineStyleSingle)
            MODIFY hExcelWks Range("A:A")::ColumnWidth(12)
            MODIFY hExcelWks Range("B:B")::ColumnWidth(10)
            MODIFY hExcelWks Range("C:C")::ColumnWidth(13)
            MODIFY hExcelWks Range("D:D")::ColumnWidth(13)
            MODIFY hExcelWks Range("E:E")::ColumnWidth(13)
            MODIFY hExcelWks Range("F:F")::ColumnWidth(10)
            MODIFY hExcelWks Range("G:G")::ColumnWidth(30)
            MODIFY hExcelWks Range("H:H")::ColumnWidth(50)
            MODIFY hExcelWks Range("I:I")::ColumnWidth(50)
            MODIFY hExcelWks Range("J:J")::ColumnWidth(50)
            MODIFY hExcelWks Range("K:K")::ColumnWidth(50)
            MODIFY hExcelWks Range("L:L")::ColumnWidth(50)
            MODIFY hExcelWks
               Range("C:C")::NumberFormat("0.00_ ;[Red]-0.00 ")
            MODIFY hExcelWks
               Range("D:D")::NumberFormat("0.00_ ;[Red]-0.00 ")
            MODIFY hExcelWks
               Range("E:E")::NumberFormat("0.00_ ;[Red]-0.00 ")
             modify hExcelWks 
              Range("A1:L1")::HorizontalAlignment = (xlCenter).

     
           modify hExcelWkb @SaveAs(by name Filename
            lk-xls-out-file,
            by name FileFormat xlExcel8,
            by name Password "",
            by name WriteResPassword "",
            by name ReadOnlyRecommended 0,
            by name CreateBackup 0)
           
           
           modify hExcelWkb @Close()
      *    Free memory.
           destroy hRange 
           destroy hFont   
           destroy hInterior
           destroy hBorders
           destroy hExcelWks
           destroy hExcelWkss
           destroy hExcelWkb
           
      *    Close Excel.
           MODIFY hExcelApp Quit()

      *    Free memory.
           destroy hExcelApp           
           
           Stop run.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: Closing Excel application after Creating

OK, good point, I have done what you suggested and now everything appears to work properly, Excel is no longer present when the program exits. Many thanks, I should have thought of that.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: Closing Excel application after Creating

I am sorry but there is nothing that stand out, except you do a lot of INQUIRE's that return an object and if that method/property is returning a new instance, you should DESTROY them, which you don't.

So, which do you have to DESTROY? You will have to read the Excel documentation for that.

As a rule, I always do a DESTROY on objects. If they are not needed, no harm done. If they are needed, I am sure I released the resources.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: Closing Excel application after Creating

Sorry, just noticed, you suggest I refer to the Excel documentation regarding what I should destroy, can you clarify exactly which document you are referring to? By the way (excuse my ignorance, but I am relatively new to this) has anyone used Freezepanes ="True" in a created Excel application, I dont normally have a problem with any inquire/modify, but cant seem to get this one right
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: Closing Excel application after Creating

Hi !

Try to do this to your excel-sheet before the quit-command:

modify hExcelWkb @Saved(1).
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: Closing Excel application after Creating

Thanks very much, but that problem is all sorted out, was just trying to avoid opening a new topic so as not to clutter up the forum, so posted a question which really didnt match the topic exactly. My bad. However, any suggestions on the Freezepanes would be appreciated
0 Likes
Highlighted
New Member.

RE: Closing Excel application after Creating

Here is what I use to freeze the first 2 rows:

     INQUIRE hExcelWks @Range("A3") IN hExcelRng.
     MODIFY  hExcelRng @Select().
     INQUIRE hExcelApp @Windows::Item(1) IN hExcelWin.
     MODIFY  hExcelWin @FreezePanes(1).
     DESTROY hExcelWin.
     DESTROY hExcelRng. 
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: Closing Excel application after Creating

Thank you very much. I have modified your code a bit to suit my app, but it works exactly as I wanted although I do it a slightly different way using @splitcolumn and @splitrow. The thing I got stuck on was trying to get a handle on class @window, in order to get at @splitcolumn, @splitrow and @freezepanes but your code INQUIRE hExcelApp @Windows::Item(1) IN hExcelWin gave me the handle I needed.

Once again, sincere thanks for your help.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: Closing Excel application after Creating

I have written an app which creates an instance of Excel which is not visible, does various things, then saves the file in format xlCSVMSDOS
Everything works perfectly except for one nuisance, in that when saving the file in this format, Excel displays the message 'Do you want to save changes you made to xxxxx' (being the filename)which requires a user response. This only happens when saving the file in .csv format, any other format such as xlExcel8 is fine and does not produce any message requiring user response . Is there any way of preventing Excel from doing this when saving as .csv, and to just save the file without requiring a user response?
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: Closing Excel application after Creating

I did a Google search and found this. Hope it helps:

How to disable save message

Here is an excerpt from the page:

Try this before the save

Application.DisplayAlerts = False

and reset it after the save

Application.DisplayAlerts = True


Paul
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.