The following code works in our NET EXPRESS code...
$set ooctrl( P)
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
******************************************************************
class-control.
MSExcel is class "$OLE$Excel.Application".
******************************************************************
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.
* Get rid of the embedded formulae by copying the first 256
* 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 "Autofit".
invoke theRange "finalize" returning theRange.
invoke WorkSheet "getRange" using z"A1:A1"
returning theRange.
invoke theRange "Select".
MOVE SPACES TO WS-PASSWORD.
IF REP-PASSWORD(1) NOT= SPACES
PERFORM GET-PASSWORD.
STRING WS-PASSWORD DELIMITED BY SPACE, x"00",
INTO WS-PASSWORD.
SET XLEXCEL7 OF ExcelFileFormat TO TRUE.
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.
STRING WS-MESSAGE-STR, " file " WS-FILENAME,
" created successfully" DELIMITED BY " " INTO
WS-MESSAGE-STR.
When this code is inserted in managed .NET code we get an COBCH0845 error "Unknown Class - "$OLE$Excel.Application" and then a load more errors relating to the various lines of code above.
How do we get managed .NET code to allow us to work with EXCEL?
How do we get managed .NET code to allow us to work with EXCEL?
Try something along these lines:
$set ilref "Microsoft.Office.Interop.Excel"
class-id TalkToExcel.
method-id Main static.
01 excelApp type Microsoft.Office.Interop.Excel.Application.
01 targetRange type Microsoft.Office.Interop.Excel.Range.
procedure division using by value p_args as string occurs any.
set excelApp to new Microsoft.Office.Interop.Excel.Application
set excelApp::Visible to true
invoke excelApp::Workbooks::Add(type Type::Missing)
set targetRange to excelApp::get_Range("A1", type Type::Missing)
invoke targetRange::set_Value(type Type::Missing, "Name")
end method.
end class.
(For some reason the forum appears to render <pre> text double-spaced. Someone should fix that...)
That's based on a Microsoft example for invoking Excel in C# 2008. (Later versions of C# have additional syntactic sugar that simplifies these operations, notably optional-parameter support that gets rid of the "Type::Missing" parameters, but I don't know of COBOL.NET equivalents.)
That code works for me - it opens Excel, creates a workbook, and sets the contents of cell A1 to the string "Name".
Note that you can get rid of the $set ilref directive if you add a reference to Microsoft.Office.Interop.Excel to your Visual Studio project.
The following code works in our NET EXPRESS code...
$set ooctrl( P)
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
******************************************************************
class-control.
MSExcel is class "$OLE$Excel.Application".
******************************************************************
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.
* Get rid of the embedded formulae by copying the first 256
* 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 "Autofit".
invoke theRange "finalize" returning theRange.
invoke WorkSheet "getRange" using z"A1:A1"
returning theRange.
invoke theRange "Select".
MOVE SPACES TO WS-PASSWORD.
IF REP-PASSWORD(1) NOT= SPACES
PERFORM GET-PASSWORD.
STRING WS-PASSWORD DELIMITED BY SPACE, x"00",
INTO WS-PASSWORD.
SET XLEXCEL7 OF ExcelFileFormat TO TRUE.
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.
STRING WS-MESSAGE-STR, " file " WS-FILENAME,
" created successfully" DELIMITED BY " " INTO
WS-MESSAGE-STR.
When this code is inserted in managed .NET code we get an COBCH0845 error "Unknown Class - "$OLE$Excel.Application" and then a load more errors relating to the various lines of code above.
How do we get managed .NET code to allow us to work with EXCEL?
OK, <pre> text is only double-spaced in the post-preview view. Still broken, but less so.
Unfortunately the column size in the form is much too narrow, and <pre> text at the default font size wraps horribly even if the lines aren't very long. (Why is the column width different in the post preview and the final view? Someone wasn't very thoughtful about their CSS settings. Also, the preview view doesn't escape less-than and greater-than characters in the text.)
Let's try that again:
$set ilref "Microsoft.Office.Interop.Excel"
class-id TalkToExcel.
method-id Main static.
01 excelApp type Microsoft.Office.Interop.Excel.Application.
01 targetRange type Microsoft.Office.Interop.Excel.Range.
procedure division using by value p_args as string occurs any.
set excelApp to new Microsoft.Office.Interop.Excel.Application
set excelApp::Visible to true
invoke excelApp::Workbooks::Add(type Type::Missing)
set targetRange to excelApp::get_Range("A1", type Type::Missing)
invoke targetRange::set_Value(type Type::Missing, "Name")
end method.
end class.
The following code works in our NET EXPRESS code...
$set ooctrl( P)
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
******************************************************************
class-control.
MSExcel is class "$OLE$Excel.Application".
******************************************************************
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.
* Get rid of the embedded formulae by copying the first 256
* 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 "Autofit".
invoke theRange "finalize" returning theRange.
invoke WorkSheet "getRange" using z"A1:A1"
returning theRange.
invoke theRange "Select".
MOVE SPACES TO WS-PASSWORD.
IF REP-PASSWORD(1) NOT= SPACES
PERFORM GET-PASSWORD.
STRING WS-PASSWORD DELIMITED BY SPACE, x"00",
INTO WS-PASSWORD.
SET XLEXCEL7 OF ExcelFileFormat TO TRUE.
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.
STRING WS-MESSAGE-STR, " file " WS-FILENAME,
" created successfully" DELIMITED BY " " INTO
WS-MESSAGE-STR.
When this code is inserted in managed .NET code we get an COBCH0845 error "Unknown Class - "$OLE$Excel.Application" and then a load more errors relating to the various lines of code above.
How do we get managed .NET code to allow us to work with EXCEL?
Just to add to the sample that Michael gave above, here is a partial conversion of your original program.
I did not run this application and there are no guarantees that it will run 100% correctly but it compiles clean and should help you to get started at least.
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 or use the $set ilref directive as Michael points out.
$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
* Get rid of the embedded formulae by copying the first 256
* columns and then doing a paste special
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
* MOVE SPACES TO WS-PASSWORD.
* IF REP-PASSWORD(1) NOT= SPACES
* PERFORM GET-PASSWORD.
* STRING WS-PASSWORD DELIMITED BY SPACE, x"00",
* INTO WS-PASSWORD.
* SET XLEXCEL7 OF ExcelFileFormat TO TRUE.
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.
The following code works in our NET EXPRESS code...
$set ooctrl( P)
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
******************************************************************
class-control.
MSExcel is class "$OLE$Excel.Application".
******************************************************************
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.
* Get rid of the embedded formulae by copying the first 256
* 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 "Autofit".
invoke theRange "finalize" returning theRange.
invoke WorkSheet "getRange" using z"A1:A1"
returning theRange.
invoke theRange "Select".
MOVE SPACES TO WS-PASSWORD.
IF REP-PASSWORD(1) NOT= SPACES
PERFORM GET-PASSWORD.
STRING WS-PASSWORD DELIMITED BY SPACE, x"00",
INTO WS-PASSWORD.
SET XLEXCEL7 OF ExcelFileFormat TO TRUE.
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.
STRING WS-MESSAGE-STR, " file " WS-FILENAME,
" created successfully" DELIMITED BY " " INTO
WS-MESSAGE-STR.
When this code is inserted in managed .NET code we get an COBCH0845 error "Unknown Class - "$OLE$Excel.Application" and then a load more errors relating to the various lines of code above.
How do we get managed .NET code to allow us to work with EXCEL?
I'm using EXCEL 2007 and this program, after a couple of minor tweaks, does not seem to want to convert the existing .CSV file into a EXCEL 2007 worksheet although it WILL create an EXCEL 2003 one. Am I missing something?
The following code works in our NET EXPRESS code...
$set ooctrl( P)
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
******************************************************************
class-control.
MSExcel is class "$OLE$Excel.Application".
******************************************************************
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.
* Get rid of the embedded formulae by copying the first 256
* 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 "Autofit".
invoke theRange "finalize" returning theRange.
invoke WorkSheet "getRange" using z"A1:A1"
returning theRange.
invoke theRange "Select".
MOVE SPACES TO WS-PASSWORD.
IF REP-PASSWORD(1) NOT= SPACES
PERFORM GET-PASSWORD.
STRING WS-PASSWORD DELIMITED BY SPACE, x"00",
INTO WS-PASSWORD.
SET XLEXCEL7 OF ExcelFileFormat TO TRUE.
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.
STRING WS-MESSAGE-STR, " file " WS-FILENAME,
" created successfully" DELIMITED BY " " INTO
WS-MESSAGE-STR.
When this code is inserted in managed .NET code we get an COBCH0845 error "Unknown Class - "$OLE$Excel.Application" and then a load more errors relating to the various lines of code above.
How do we get managed .NET code to allow us to work with EXCEL?
Try changing the second parameter to SaveAs to one of the following:
type XlFileFormat::xlOpenXMLWorkbook *> equals number 51 or default for Excel 2007
equates to .xlsx extension
type XlFileFormat::xlOpenXMLWorkbookMacrosEnabled *> equals number 52 or Excel 2007 with macros
equates to .xlsm extension
type XlFileFormat::xlWorkbookDefault *> equals number 51 (see above) if running under Excel 2007
equates to .xlsx extension
