Skip to main content

Using EXCEL in managed code.

  • September 19, 2012
  • 7 replies
  • 0 views
D

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?

    7 replies

    D

    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.

    D

    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.

     

    Chris Glazier
    Forum|alt.badge.img+2
    • Chris GlazierModerator
    • Moderator
    • September 19, 2012

    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.

    D

    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?

    Chris Glazier
    Forum|alt.badge.img+2
    • Chris GlazierModerator
    • Moderator
    • September 21, 2012

    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

    D

    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?

    Thank You.

    D

    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?

    Thank You.

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement