Created On:  19 September 2012

Problem:

Customer has a Net Express native code application that uses the Net Express OLE Domain class library support to program Excel OLE Automation in COBOL.  Customer tried to move this into a .NET managed code program in Visual COBOL and it doesn't compile.  How can he use Excel OLE Automation from within a managed code program?

The following code works in Net Express:

$set ooctrl(+P)
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
******************************************************************
class-control.
MSExcel is class "$OLE$Excel.Application".
*****************************************************************
data division.
working-storage section.
01 WS-CSVFILE  pic x(30).
01 WS-XLSFILE  pic x(30).
01 WS-PASSWORD  pic x(20).
01 ExcelObject   object reference.
01 WorkBooksCollection object reference.
01 Workbook object reference.
01 WorkSheets object reference.
01 Worksheet object reference.
01 theRange   object reference.
Procedure Division
*> Create a new instance of Microsoft Excel
     invoke MSExcel "new" returning ExcelObject.
*> Make Excel visible
     invoke ExcelObject "setVisible" using by value 1.
*> Get the collection of WorkBooks
     invoke ExcelObject "getWorkBooks"
         returning WorkBooksCollection.
*> Open File
     invoke WorkBooksCollection "Open"
          using by reference WS-CSVFILE
          returning Workbook.
     invoke Workbook "getWorkSheets" returning WorkSheets.
     invoke WorkSheets "getItem" using by value 1
         returning WorkSheet.
     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 "Autofit".
     invoke theRange "finalize" returning theRange.
     invoke WorkSheet "getRange" using z"A1:A1"
        returning theRange.
     invoke theRange "Select".
     invoke WorkSheet "SaveAs" using
        by reference WS-XLSFILE
        by value ExcelFileFormat
        by reference WS-PASSWORD.
     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.

Resolution:

Here is a conversion of the original native program so that it will work in managed code.

The $OLE$ class support in Net Express is only available under native code in Visual COBOL.

If you wish to use Excel in managed code then you need to add a reference to the project using the COM tab to Microsoft.Office.Interop.Excel.

$set ilusing"Microsoft.Office.Interop"
program-id. Program1 as "Excelmanaged.Program1".
data division.
working-storage section.
01 WS-CSVFILE string.
01 WS-XLSFILE string.
01 WS-PASSWORD string.
01 ExcelObject type Microsoft.Office.Interop.Excel.Application.
01 WorkBooksCollection type Workbooks.
01 WorkBook type _Workbook.
01 Worksheets type Sheets.
01 Worksheet type _Worksheet.
01 theRange type Range.
procedure division.
    set ExcelObject to new type Microsoft.Office.Interop.Excel.Application
*> Make Excel visible
    set ExcelObject::Visible to true
*> Get the collection of WorkBooks
    set WorkBooksCollection to ExcelObject::Workbooks
*> Open File
    set Workbook to WorkBooksCollection::Open(WS-CSVFILE, 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
    set theRange to Worksheet::get_Range("A", "IV")
    invoke theRange::Select
    invoke theRange::Copy(type Type::Missing)
    invoke theRange::PasteSpecial(type XlPasteType::xlPasteValues, type XlPasteSpecialOperation::xlPasteSpecialOperationNone,
        type Type::Missing, type Type::Missing)
    invoke theRange::AutoFit
    set theRange to WorkSheet::get_Range("A1", "A1")
    invoke theRange::Select
    invoke WorkSheet::SaveAs(WS-XLSFILE, type XlFileFormat::xlExcel7, WS-PASSWORD, type Type::Missing, type Type::Missing
        type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing)
    invoke ExcelObject::Quit
   goback.

end program Program1.