Highlighted
Contributor.
Contributor.
1242 views

Extract data from Excel with Micro Focus COBOL?

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?

0 Likes
5 Replies
Highlighted
Outstanding Contributor.
Outstanding Contributor.

RE: Extract data from Excel with Micro Focus COBOL?

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
0 Likes
Highlighted
Contributor.
Contributor.

RE: Extract data from Excel with Micro Focus COBOL?

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?
0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Extract data from Excel with Micro Focus COBOL?

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.
0 Likes
Highlighted
Contributor.
Contributor.

RE: Extract data from Excel with Micro Focus COBOL?

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.
0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Extract data from Excel with Micro Focus COBOL?

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:

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.