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.