Accessing Excel worksheets using COM

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:

The zip file contains the following::

  1. Form F_EXCEL allows user dialog.
  2. Form FB_EXCEL which performs actions using the COM signatures.
  3. Signatures for EXCEL, RANGE, WORKBOOKS, WORKBOOK, WORKSHEETS, WORKSHEET.
  4. Excel file TEST.XLS (which contains macros MyMacro and CSV).
  5. 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 NumberThe sheet number to be accessed within the Excel file.
Start RowThe start row (i.e. which row to start populating from).
RowsThe numbers of rows in DATA.
Columnsthe number of columns in DATA.
DataIndexed list of values with GOLD; separator (note 15 items in this example, 3 x 5).
Macro NameName of the macro within the XLS file to be run.
Macro ParametersParameter 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@marston-home.demon.co.uk
mailto:TonyMarston@hotmail.com
http://www.tonymarston.net

counter