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 

  • 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?

  • 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

  • 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

  • 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

  • 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.

  • 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

  • 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.

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