How to open Excel file and get a Cell Value (OLE Automation)

0 Likes

Hi everyone,

Haven't seen an example from NetExpress product that can;

1. open an Excel file (say; myfigure.XLSX)
2. go to a certain cell (say; A3)
3. Then get the cell value and save it in a .DAT cobl datafile

Anyone here?  Thank you very much.

infoRene

Labels:

Knowledge Docs
Comment List
  • Thank you Chris for the sample code.... you are always a great help

    Thank you again :-)

  • Hi,

    Sorry, I didnt see this sooner but you should have posted this question on the Discussions section of the forum so I get notified.

    Here is an example:

          *----------------------------------------------------------------*
          *                      testExcel                                 *
          *                                                                *
          *   This program demonstrates how to open up an existing Excel   *
          *   Workbook, then in a loop, read the first 3 cells and display *
          *   their values. It then will read a cell directly using a range*
          *   object and display its value.                                *
          *                                                                *
          *   To run this you will have to create an Excel spreadsheet and *
          *   populate cells A1 thru C3 with a string value. You can use a *
          *   value such as "cell A1", "cell A2", etc. Be sure to change   *
          *   the value in ExcelFileName to match the name you are using.  *
          *----------------------------------------------------------------*
          $set ooctrl(+P)
           id division.
           program-id.  testExcel.
           class-control.
               MSExcel is class "$OLE$Excel.Application".
    
           working-storage section.
           01 ExcelObject          object reference.
           01 WorkBooksCollection  object reference.
           01 WorkBook             object reference.
           01 Cell                 object reference.
           01 CellRange            object reference.
    
           01 LoopCount            pic xx comp-5.
           01 ExcelFileName        pic x(256)
              value z"d:\tests\testexcel\myworkbook.xlsx".
           01 CellValue            pic x(20) value spaces.
           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
    
                invoke WorkbooksCollection "Open"
                   using ExcelFileName
                   returning Workbook
                end-invoke
    
          *>   Now loop, reading cells A1, B1 and C1
          *>   first value is column number second is row
               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 "getValue" returning CellValue
                  display "Cell value = " CellValue
                  invoke Cell "finalize" returning Cell
               end-perform
    
          *>   Read the cell by it's name instead using a Range
               invoke ExcelObject "getRange"
                  using z"C3"
                  returning CellRange
               end-invoke
               invoke CellRange "getValue" returning CellValue
               display "Cell C3 = " CellValue
    
          *>   Close the WorkBook
               invoke WorkBook "Close" using by value 0
    
          *>   Finalize all objects
               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.
    

    Chris Glazier
    Global Technical Support | OpenText


    If you found this post useful, give it a “Like” or click on "
    Verify Answer" under the "More" button

  • Oh I forgot... I am still using NetExpress v3.1.11 (Merant) with an example folder under C:\Merant\Base\DEMO\OLEDEMOS\EXCEL

Related
Recommended