Skip to main content

Extract data from Excel with Micro Focus COBOL?

  • February 4, 2018
  • 5 replies
  • 1 view
A
  • Anonymous

I have a PC with Windows 10, running Micro Focus Visual COBOL for Visual Studio 2010, and running Excel 2010.

 I want extract data from Excel workbooks and write it to an Ascii file.  I've got a few questions.

 Program would read through a folder full of Excel workbooks, like

     18BC2487.xlsx

     18BC1924.xlsx

     . . . etc.

 

Each workbook has ten sheets, two of which contain five items that I want to extract.

      CEO sheet, which contains

             CEO's name in cell B6

             CEO's salary in cell B7

      Company sheet, which contains

              Number of staff in cell C22

              City in cell C23

              State in cell C24

 

QUESTIONS.  Can this be done with my existing Micro Focus setup?

                     If not, what other Micro Focus software would I need?  How much would it cost?

                     Where can I find sample Micro Focus COBOL code that performs a similar task as that described above?

    5 replies

    Claude Greiner
    • Claude Greiner
    • Participating Frequently
    • February 4, 2018

    I have a PC with Windows 10, running Micro Focus Visual COBOL for Visual Studio 2010, and running Excel 2010.

     I want extract data from Excel workbooks and write it to an Ascii file.  I've got a few questions.

     Program would read through a folder full of Excel workbooks, like

         18BC2487.xlsx

         18BC1924.xlsx

         . . . etc.

     

    Each workbook has ten sheets, two of which contain five items that I want to extract.

          CEO sheet, which contains

                 CEO's name in cell B6

                 CEO's salary in cell B7

          Company sheet, which contains

                  Number of staff in cell C22

                  City in cell C23

                  State in cell C24

     

    QUESTIONS.  Can this be done with my existing Micro Focus setup?

                         If not, what other Micro Focus software would I need?  How much would it cost?

                         Where can I find sample Micro Focus COBOL code that performs a similar task as that described above?

    This is not a problem, Micro Focus have any examples on the web. Otherwise send me a email at cgreiner@cgcs.de and we can Exchange our sources and experience
    A
    • Anonymous
    • February 4, 2018

    I have a PC with Windows 10, running Micro Focus Visual COBOL for Visual Studio 2010, and running Excel 2010.

     I want extract data from Excel workbooks and write it to an Ascii file.  I've got a few questions.

     Program would read through a folder full of Excel workbooks, like

         18BC2487.xlsx

         18BC1924.xlsx

         . . . etc.

     

    Each workbook has ten sheets, two of which contain five items that I want to extract.

          CEO sheet, which contains

                 CEO's name in cell B6

                 CEO's salary in cell B7

          Company sheet, which contains

                  Number of staff in cell C22

                  City in cell C23

                  State in cell C24

     

    QUESTIONS.  Can this be done with my existing Micro Focus setup?

                         If not, what other Micro Focus software would I need?  How much would it cost?

                         Where can I find sample Micro Focus COBOL code that performs a similar task as that described above?

    I'm good at COBOL and good at Excel, but haven't a clue how to make COBOL get data from Excel. Could you point me to some Micro Focus examples, please?
    Chris Glazier
    Forum|alt.badge.img+2

    I have a PC with Windows 10, running Micro Focus Visual COBOL for Visual Studio 2010, and running Excel 2010.

     I want extract data from Excel workbooks and write it to an Ascii file.  I've got a few questions.

     Program would read through a folder full of Excel workbooks, like

         18BC2487.xlsx

         18BC1924.xlsx

         . . . etc.

     

    Each workbook has ten sheets, two of which contain five items that I want to extract.

          CEO sheet, which contains

                 CEO's name in cell B6

                 CEO's salary in cell B7

          Company sheet, which contains

                  Number of staff in cell C22

                  City in cell C23

                  State in cell C24

     

    QUESTIONS.  Can this be done with my existing Micro Focus setup?

                         If not, what other Micro Focus software would I need?  How much would it cost?

                         Where can I find sample Micro Focus COBOL code that performs a similar task as that described above?

    The manner in which you access Excel depends on whether you are compiling to native code or to managed .NET code. If you are compiling to native then you would access the Microsoft Office Interop Excel COM server thru the Visual COBOL set of OLE classes. If you are compiling to managed .NET you would use the equivalent .NET set of Interop classes for Excel. You could also use OpenESQL to open the workbook thru ODBC and read the data as if it were a database table.

    The Net Express product actually had some examples of accessing Excel in the COMDEMOS folder under the installed samples. These sadly, did not make it to Visual COBOL. There are examples of this in our knowledgebase which is also available from this forum and also the Net Express/Server Express forum. If you go to the top of this page and enter "Excel" into the search box and press Enter you should see a number of articles that have examples attached. If you still have the NX product installed you can find the examples in:
    C:\\Program Files (x86)\\Micro Focus\\Net Express 5.1\\Examples\\Net Express IDE\\COMDEMOS\\EXCEL

    Thanks.
    A
    • Anonymous
    • February 7, 2018

    I have a PC with Windows 10, running Micro Focus Visual COBOL for Visual Studio 2010, and running Excel 2010.

     I want extract data from Excel workbooks and write it to an Ascii file.  I've got a few questions.

     Program would read through a folder full of Excel workbooks, like

         18BC2487.xlsx

         18BC1924.xlsx

         . . . etc.

     

    Each workbook has ten sheets, two of which contain five items that I want to extract.

          CEO sheet, which contains

                 CEO's name in cell B6

                 CEO's salary in cell B7

          Company sheet, which contains

                  Number of staff in cell C22

                  City in cell C23

                  State in cell C24

     

    QUESTIONS.  Can this be done with my existing Micro Focus setup?

                         If not, what other Micro Focus software would I need?  How much would it cost?

                         Where can I find sample Micro Focus COBOL code that performs a similar task as that described above?

    Thanks for your detailed response, Chris. I'll be copying this thread to my sidekick Emi, who is more tech-savvy than me. We'd like to narrow down our question a bit.

    Our goal is to read certain data elements from every .xlsx workbook in a folder, referencing sheet names and cell addresses. We would write the results to a data file, producing one record per Excel workbook. Here are considerations / restraints:
    (1) We've got Micro Focus Visual COBOL (v 2.2) for Visual Studio 2010, running in Windows 10.
    (2) We can't change the workbooks to .xlm or any other format. We must take them as they are.
    (3) We compile our programs to native code.
    (4) No, we no longer have Net Express.

    Given those parameters, <I think> you recommended that we "would access the Microsoft Office Interop Excel COM server thru the Visual COBOL set of OLE classes". Please give specific details about how we would do that – compiler directives, URLs, statements in COBOL, etc. Plus whatever clarifying documentation might exist.

    Thanks again.
    Chris Glazier
    Forum|alt.badge.img+2

    I have a PC with Windows 10, running Micro Focus Visual COBOL for Visual Studio 2010, and running Excel 2010.

     I want extract data from Excel workbooks and write it to an Ascii file.  I've got a few questions.

     Program would read through a folder full of Excel workbooks, like

         18BC2487.xlsx

         18BC1924.xlsx

         . . . etc.

     

    Each workbook has ten sheets, two of which contain five items that I want to extract.

          CEO sheet, which contains

                 CEO's name in cell B6

                 CEO's salary in cell B7

          Company sheet, which contains

                  Number of staff in cell C22

                  City in cell C23

                  State in cell C24

     

    QUESTIONS.  Can this be done with my existing Micro Focus setup?

                         If not, what other Micro Focus software would I need?  How much would it cost?

                         Where can I find sample Micro Focus COBOL code that performs a similar task as that described above?

    WPautomation.pdf

    Yes, for native programs you would use the Interop Excel COM server thru Visual COBOL OLE classes. I am attaching an old .PDF document that was written for use with Net Express but still applies to Visual COBOL. It describes the COM automation models and gives examples of how to access these in COBOL.

    The actual classes and methods that are used are provided with Excel and are documented by Microsoft. You would look under Visual Basic for Applications (VBA) using the Excel Application Object model. An example of these docs can be found here.

    If you search the Net Express forum on this community site for the term "Excel" you should also be able to find some examples.

    Although the OLE class library docs are not directly within the Visual COBOL docs the help is available in the file nxrclr.chm, which is installed in the Help folder of your installation. The default location is%ProgramFiles(x86)%\\Micro Focus\\Visual COBOL\\Help.

    You can also still access the Net Express Help for this on-line here:

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement