Proc code - Solutions - Accessing Excel worksheets using COM Go Home

This demonstration software has been put by Michael J Levene to show how to access Excel files from within UNIFACE using COM. His sample software can be downloaded from here (24KB 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 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 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/