Tony Marston - 1st August 2002

This demonstration software has been put together by Michael J Levene to show how to access Excel files from within UNIFACE using COM. His sample software can be downloaded from here (25KB zipped).

This software shows how to do the following:

Insert data into a worksheet (multiple rows and columns)
Call a macro in a worksheet
Load data into a worksheet from an external .CSV file

The zip file contains the following::

Form F_EXCEL allows user dialog.
Form FB_EXCEL which performs actions using the COM signatures.
Signatures for EXCEL, RANGE, WORKBOOKS, WORKBOOK, WORKSHEETS, WORKSHEET.
Excel file TEST.XLS (which contains macros MyMacro and CSV).
Text file TEST.CSV which contains sample data.

You must compile both forms and all signatures before this software will run. Form F_EXCEL should appear as in figure 1:

Figure 1 - form F_EXCEL

tip39_01

The field names are used as follows:

Filename (XLS)	Name of the Excel file. Note that this must be amended to show the full path name to the file on your PC.
Sheet Number	The sheet number to be accessed within the Excel file.
Start Row	The start row (i.e. which row to start populating from).
Rows	The numbers of rows in DATA.
Columns	the number of columns in DATA.
Data	Indexed list of values with GOLD; separator (note 15 items in this example, 3 x 5).
Macro Name	Name of the macro within the XLS file to be run.
Macro Parameters	Parameter to pass to the macro. How you format it and what you use it for with Excel is up to you.
Filename (CSV)	Name of a comma-separated-variable file containing data to be loaded into the Excel file. Note that this must be amended to show the full path name to the file on your PC

The command buttons on the bottom of the screen will perform the following:

Populate/Save/Close	Open the specified worksheet within the XLS file, insert DATA starting at the specified row, splitting it across the specified number of rows and columns, save and close the file.
Macro/Save/close	Open the specified worksheet within the XLS file, call the named macro with the parameter, save and close the file.
Populate from CSV/Save/Close	Open the specified worksheet within the XLS file, call macro CSV with the name of the CSV file (will insert the data starting at the specified row number), save and close the file.

You are free to use the software without warranty or support, at your own risk.

If you have any comments on this software then please contact Michael Levene at:
mailto:mlevene@computalink-systems.com
http://www.computalink-systems.com

Tony Marston
1st August 2002

mailto:tony@tonymarston.net
mailto:TonyMarston@hotmail.com
http://www.tonymarston.net

counter

Accessing Excel worksheets using COM

Tony Marston - 1st August 2002