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 

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

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

Children
No Data