Sample 24 is a program that uses the COBOL COM client early binding function to operate Excel.
The COBOL COM client function creates a COM object to use functions which the COM server supports in the same way as the COBOL method.
Excel is an independent application and also functions as a COM server. So, an application that operates Excel can be written with COBOL.
To execute this program, the following products are necessary:
Microsoft(R) Excel
"Excel" is used here as a generic name for these products.
For details on the COBOL COM function, refer to Chapter 24 in the "NetCOBOL User’s Guide".
Overview
The sample program performs the operations listed below for Excel from a COBOL application.
Excel activation and termination
Excel sheet open
Work sheet selection and data insertion
Graph creation
Printing of selected sheet
The COBOL COM client function supports the early binding and late binding methods to recognize COM server methods and interfaces.
Early binding is used in this sample.
When early binding is used, object property and in-line method invocation can be written. Also, the performance is better than that of late binding. However, the early binding method requires a type library during development and is liable to be influenced by COM server modifications. This program uses the COM server name EXCEL to use the Excel functions. An Excel type library must be specified for this COM server name before compiling the program. Excel97 cannot be operated with an executable file built by referencing an Excel2000 type library. Also, Excel2000 cannot be used with an executable file built by referencing an Excel97 type library.
For details of late binding, see Sample 23, "COM Program to Control Excel (Late Binding)."
Available Programs
SAMPLE24.PRJ (Project file)
SAMPLE24.COB (COBOL source file)
GRAPHDATA.XLS (Excel file for test)
Applicable COBOL Functions
COM client function
Project management function
Applicable COBOL Statements
The DISPLAY, IF, INVOKE, PERFORM, and SET statements are used.
Building and Rebuilding
The Project Manager's Build function is used to compile and link this program.
Folder C:\NetCOBOL is assumed below as the location where NetCOBOL is installed.
Change the folder name C:\NetCOBOL to the name of the folder where NetCOBOL is installed.
Start the Project Manager.
Open the project file SAMPLE24.PRJ.
Select a project file and select "Compiler Options" from the "Project-Option" menu.
The "Compiler Options" dialog is displayed.
Click the "COM Server Name" button in the "Compiler Options" dialog.
The "COM Server Setting" dialog is displayed.
Set data as shown below, assuming that type library Excel is installed in the "C:\Program Files\Microsoft Office" with COM server name EXCEL.
EXCEL = C:\Program Files\Microsoft Office\Office\Excel8.olb
If type library Excel2000 is used (file name: Excel9.olb) or Excel is installed in another folder, click the Change button to change the type library setting.
Select "Build" from the "Project" menu.
After build termination, check that SAMPLE24.EXE is created.
Note
When you use Excel2002 or later, following modification is necessary.
Program
Modify "sample24.cob" as following.
001040 INVOKE OBJ-CELL "SET-VALUE" 001050 USING PDATA(CELL-COL) 001060* If you use EXCEL2002 or later, replace the above statement with 001061* this statement: 001070* INVOKE OBJ-CELL "SET-VALUE" 001080* USING OMITTED PDATA(CELL-COL)
Comment out line 1040 and 1050.
Validate the comment line 1070 and 1080.
001040* INVOKE OBJ-CELL "SET-VALUE" 001050* USING PDATA(CELL-COL) 001060* If you use EXCEL2002 or later, replace the above statement with 001061* this statement: 001070 INVOKE OBJ-CELL "SET-VALUE" 001080 USING OMITTED PDATA(CELL-COL)
Type library
The type library of Excel2002 or later is Excel.exe. Set COM server name as shown below, assuming that Excel2002 is installed in the "C:\Program Files\Microsoft Office".
EXCEL = C:\Program Files\Microsoft Office\Office\Excel.exe
Executing the Program
Select "Execute" on the "Project" menu.
Excel is started. The following message is displayed on the COBOL console and the system waits for input.
Use the full path-name to specify an Excel file for the test.
The specified Excel file is opened and the program controls Excel. The following message is displayed on the COBOL console and the system waits for input again.
Input the type of a graph to be created.
The specified graph is plotted. The following message is displayed on the COBOL console and the system waits for input again.
If the graph is to be printed, specify a printer name. If it is not to be printed, press the ENTER key without inputting data.
Excel is terminated and the program is terminated. The graph is printed before terminating Excel when a printer name is specified.
