Created On:  19 July 2012

Problem:

How can I create a new Excel spreadsheet, populate it with data, prompt the user for a filename to save it under and then save it using that filename, all automated from within a Net Express or Visual COBOL native program?

Resolution:

This can be done by using the COM Automation client support for native COBOL within both Net Express and Visual COBOL programs.  The following is a modified version of the EXCELdemo that is supplied with Net Express.

It uses the getSaveAsFilename method to prompt the user for a filename and then uses the SaveAs method to actually save the file.

This demo also shows how to use the named parameter support in COM method calls in order to pass parameters by name rather than by order.

      *-------------------------------------------------------------------------
      *                                   Save Excel File                                         
      *                                                                                                   
      * This example program demonstrates how to use the Excel COM 
      * server support to create a new spreadsheet, populate it with         
      * graph data and then prompt the user for a filename using the         
      * GetSaveAsFileName method and then save it using the SaveAs          
      * method.                                                                                             
      *                                                                                                        
      * This is a modified version of the demo that is supplied with                
      * Net Express under the C:\Program Files (x86)\Micro Focus\Net        
      * ress 5.1\Examples\Net Express IDE\COMDEMOS\EXCEL folder.  
      *                                                                
      * Also demonstrated is the support for using Named Parameters    
      * in the COM methods.                                            
      *-------------------------------------------------------------------------
      * following directive is required for COM clients
      $set ooctrl(+P)
       class-control.
           MSExcel is class "$OLE$Excel.Application"
           olesup is class "olesup".

       working-storage section.
       01 ExcelObject          object reference.
       01 WorkBooksCollection  object reference.
       01 WorkBook             object reference.
       01 Cell                 object reference.
       01 CellRange            object reference.
       01 ChartsCollection     object reference.
       01 Chart                object reference.

       01 LoopCount            pic xx comp-5.
       01 SaveName             pic x(256)      value spaces.
       01 lstype               pic x(4) comp-5 value 0.
       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
           end-invoke

      *>   Add a new WorkBook to the collection
           invoke WorkBooksCollection "Add"
              returning WorkBook
           end-invoke

      *>   Now loop, filling in the cells A1, B1 and C1 with
      *>   the numbers 1, 2 and 3 respectively
           perform varying LoopCount from 1 by 1
              until LoopCount > 3
              invoke ExcelObject "getCells"
                 using by value 1
                       by value LoopCount
                 returning Cell
              end-invoke
              invoke Cell "setValue" using by value LoopCount
              invoke Cell "finalize" returning Cell
           end-perform

      *>   Select the range of cells that we have just populated
           invoke ExcelObject "getRange"
              using z"A1:C1"
              returning CellRange
           end-invoke

           invoke CellRange "Select"

      *>   Get the collection of charts
           invoke ExcelObject "getCharts" returning ChartsCollection

      *>   Add a new chart to the collection
           invoke ChartsCollection "Add" returning Chart

      *>   Set the chart type to be 3D-Column
           invoke Chart "setType" using by value -4100

      *>   Tell OLE support that next COM call will use 1 named parameter
      *>   This is useful for calling COM methods that have optional
      *>   parameters.
           invoke olesup "setNamedParameters" using by value 1 size 4
      *>   Next call is required because getSaveAsFilename is method
      *>   that starts with get - don't treat as property.
           invoke olesup "setDispatchType" using by value 0 size 4
      *>   When named parameters are called each parameter is preceded
      *>   by a string containing name of parameter to follow it
           invoke ExcelObject "getSaveAsFilename"
              using z"FileFilter"
                 z"Excel Files (*.xlsx), *.xlsx, All Files (*.*), *.*"
              returning SaveName
           end-invoke
           invoke olesup "setNamedParameters" using by value 1 size 4
           invoke WorkBook "SaveAs"
              using z"Filename"    *>null terminated string
                    savename
           end-invoke

      *>   Close the WorkBook, discarding the contents
           invoke WorkBook "Close" using by value 0

      *>   Finalize all objects
           invoke Chart "finalize" returning Chart
           invoke ChartsCollection "finalize" returning ChartsCollection
           invoke CellRange "finalize" returning CellRange
           invoke WorkBook "finalize" returning WorkBook
           invoke WorkBooksCollection "finalize"
              returning WorkBooksCollection
           end-invoke

      *>   Exit Excel
           invoke ExcelObject "Quit"
           invoke ExcelObject "Finalize" returning ExcelObject

           stop run.