Highlighted
Visitor.
3489 views

release Excel from memory in Visual Cobol

Hi all,

Many thanks for all the useful posts over the last while which let me pick up some great hints and tips.

We ported our application from Net Express to Visual Cobol and made various changes to some programs that interface with Excel. I have been unable to figure out how to release Excel from memory in the Visual Cobol code though and would be very grateful if anyone can help.

The earlier Net Express code let me release Excel by cycling through the used objects with invoke Worksheet "finalize" returning Worksheet and so on, but I am not sure what the equivalent is in Visual Cobol.

Many thanks,

John 

0 Likes
8 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: release Excel from memory in Visual Cobol

The interface should be the same under Visual COBOL if you are still using a native code project along with the COM support. Have you rewritten the Excel piece in managed code using the Microsoft Office Interop classes now?

Highlighted
Visitor.

RE: release Excel from memory in Visual Cobol

Hi Chris,

Many thanks for getting back to me.

Yes, we did change everything to use the MS-Office Interop classes.

I looked at a few VB and C# forums to try work out how to replicate things like "Marshal" or "GC (Garbage Collection)" in the Visual Cobol code but am a bit baffled at the moment. If it proves to be a few hoops to jump through then I'll look at going back to the COM support in the native code project.

John

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: release Excel from memory in Visual Cobol

You can release the memory on the COM objects created from the Excel interop class using something like:

$set ilusing"System.Runtime.InteropServices"

          Invoke type Marshal::FinalReleaseComObject(myworkbook)

     *   invoke type Marshal::FinalReleaseComObject(myworkbooks)

etc...          

         invoke type GC::Collect

0 Likes
Highlighted
Visitor.

RE: release Excel from memory in Visual Cobol

Hi Chris,

Thanks again for that info ... I will give it a try later today.

I didn't used the word "type" between the "Invoke" and "Marshal" which is probably why I was getting  verb/operand unrecognised errors.

I'll let you know how I get on with using the code you provided.

John

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: release Excel from memory in Visual Cobol

The word type is required in COBOL because you are invoking the static method FinalReleaseComObject in the class Marshal. You always need to use type when what follows it is a direct class name and not an object. If you are invoking an object then you do not use type.

Highlighted
Visitor.

RE: release Excel from memory in Visual Cobol

Sorry Chris, I haven't had time today to fully investigate the changes required. What I need to do tomorrow is try count the number of invokes which should then tell me the number of Marshal::FinalReleaseComObject instances required.

Have a good evening,

John

0 Likes
Highlighted
Visitor.

RE: release Excel from memory in Visual Cobol

Hi Chris and everyone,

Hope you had a nice weekend.

I've looked at the issue of releasing Excel from memory but have been unable to get it working unfortunately. If no-one minded, could you have a look at my code below to see where I'm going wrong in Visual Cobol? Many thanks!

From what I read, I need to release all open objects but am not familiar enough with object coding and memory releasing to count/track what objects are open or not in memory.

I've shown the Net Express code first which does work and releases Excel from memory. I'm trying to get Visual Cobol do the same and release Excel from memory as well. We are on Visual Cobol 2.1 due to the machine this code will run on still being Windows XP.

Thanks again,

John

     * NET EXPRESS CODE

     * ----------------

      CONVERT-CSV-TO-EXCEL SECTION.

      RF010.

     *  Create a new instance of Excel

          invoke MSExcel "new" returning ExcelObject.

          invoke ExcelObject "getWorkBooks"

              returning WorkBooksCollection.

          invoke WorkBooksCollection "Open"

                                       using by reference Filename-x1

                                       returning Workbook.

          invoke Workbook "getWorkSheets" returning WorkSheets.

          invoke WorkSheets "getItem" using by value 1

                                returning WorkSheet.

     *  Get rid of the embedded formulae by copying

     *  columns and then doing a paste special

          invoke WorkSheet "getColumns" using z"A:IV"

                                        returning theRange.

          invoke theRange "Select".

          invoke theRange "Copy".

          invoke theRange "PasteSpecial" using

                                           by value -4163

                                           by value -4142.

          invoke theRange "finalize" returning theRange.

     *  Resize the columns (Autofit) and position to cell A1

          invoke WorkSheet "getColumns" using z"A:IV"

                                        returning theRange.

          invoke theRange "Select".

          invoke theRange "Autofit".

          invoke ExcelObject "getRange" using z"A1"

                                        returning CellRange.

          invoke CellRange "Select".

     *  Save the .csv file (LINK-FILENAME) as .xls (FILENAME-X2)

          STRING WS-DIRECTORY-NAME DELIMITED BY SPACE, "\",

               LINK-FILENAME DELIMITED BY " " x"00" INTO FILENAME-X2.

          SET XLEXCEL7 OF ExcelFileFormat TO TRUE.

          invoke WorkSheet "SaveAs" using

                 by reference FILENAME-X2

                 by value ExcelFileFormat.

     *  Close the workbook and release (Excel) objects from memory

          invoke WorkBook "Close" using by value 1.

          invoke CellRange "finalize" returning CellRange.

          invoke theRange "finalize" returning theRange.

          invoke WorkSheet "finalize" returning Worksheet.

          invoke WorkSheets "finalize" returning WorkSheets.

          invoke Workbook "finalize" returning WorkBook.

          invoke WorkbooksCollection "finalize"

                      returning WorkBooksCollection.

          invoke ExcelObject "Quit".

          invoke ExcelObject "Finalize" returning ExcelObject.

      RF999.

          EXIT.

     * VISUAL COBOL CODE

     * -----------------

      CONVERT-CSV-TO-EXCEL SECTION.

      RF010.

     *>  Create a new instance of Excel

          set ExcelObject to new type Microsoft.Office.Interop.Excel.Application

     *>  Get the collection of WorkBooks

          set WorkBooksCollection to ExcelObject::Workbooks

     *>  Open File

          set Workbook to WorkBooksCollection::Open(FILENAME-X1, type Type::Missing, type Type::Missing, type Type::Missing,

            type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing,

            type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing)

          set Worksheets to Workbook::Worksheets

          set Worksheet to Worksheets[1] as type _Worksheet

     *>  Get rid of the embedded formulae by copying columns and then doing a paste special

          invoke Worksheet::Columns::Select()

          invoke Worksheet::Columns::Copy(type Type::Missing)

          set theRange to WorkSheet::Range("A1", "A1")

          invoke theRange::PasteSpecial(type XlPasteType::xlPasteValues,

            type XlPasteSpecialOperation::xlPasteSpecialOperationNone, type Type::Missing, type Type::Missing)

     *>  Release theRange      

          invoke type Marshal::FinalReleaseComObject(theRange)      

     *>  Resize the columns (Autofit) and position to cell A1

          set theRange to WorkSheet::Range("A1", "IV1")

          invoke theRange::EntireColumn::AutoFit

          set theRange to WorkSheet::Range("A1", "A1")

          invoke theRange::Select

     *  Save the .csv file (LINK-FILENAME) as .xls (FILENAME-X2)

          STRING WS-DIRECTORY-NAME DELIMITED BY SPACE, "\",

              LINK-FILENAME DELIMITED BY " " x"00" INTO FILENAME-X2.

          invoke WorkSheet::SaveAs(FILENAME-X2, type XlFileFormat::xlExcel7, type Type::Missing, type Type::Missing, type Type::Missing

            type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing)

     *  Close the workbook and release (Excel) objects from memory

          Invoke ExcelObject::Workbooks::Close()

          Invoke type Marshal::FinalReleaseComObject(theRange)

          Invoke type Marshal::FinalReleaseComObject(Worksheet)

          Invoke type Marshal::FinalReleaseComObject(Worksheets)

          Invoke type Marshal::FinalReleaseComObject(Workbook)

          Invoke type Marshal::FinalReleaseComObject(WorkbooksCollection)

          invoke ExcelObject::Quit.

          invoke type Marshal::FinalReleaseComObject(ExcelObject)

     *>  Garbage collection

          Invoke type GC::Collect

          Invoke type GC::WaitForPendingFinalizers().

      RF999.

          EXIT.

0 Likes
Highlighted
Visitor.

RE: release Excel from memory in Visual Cobol

Sorry, I forgot to say that the Visual Cobol program does have    $set ilusing "System.Runtime.InteropServices"

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.