Using Automation To Access Excel

Automation from a Workspace

You can access COM components from a Smalltalk MT workspace. This is a simple way to test and refine your COM code. It also allows you to see methods and properties that a COM component supports.

Here I will do an example that uses Excel to demonstrate the techniques.

Open a workspace (Ctrl-N). Type and execute:

pExcel := IDispatch createObject: 'Excel.Application'.

This creates a new instance of Excel (use getObject: to access an existing running instance). Inspect (Ctrl-Q) pExcel and you will see that it is an instance of IDispatch. You will be shown all of the functions (FUNC) and properties (GET/PUT) that this interface supports (note change sorting by clicking on the headings of the table).

Look in this table for Visible. Notice that you can GET and PUT this property. By default Excel starts as an Automation client as invisible. Let's make this instance visible.

pExcel putProperty: 'Visible' value: true.

Excel will now appear. Notice that there are no workbooks. We need to create a workbook. If you look back at the pExcel table you will find Workbooks with a GET type. This is because you can only get this property. Workbooks returns the Excel collection of Workbooks.

pWorkbooks := pExcel getProperty: 'Workbooks'.

Given this collection, we can now add a workbook.

pWorkbook := pWorkbooks invoke: 'Add'.

You will now see a Workbook appear in Excel. By default this workbook has three sheets (see the tabs along the bottom). We want to gain access to the first worksheet. As before (with workbooks) we need to fetch the worksheet collection.

pSheets := pWorkbook getProperty: 'Worksheets'.

Collections have a property called Item that allow the selection of an element in the collection. Use this property to fetch the first worksheet (element 1).

pWorksheet := pSheets getProperty: 'Item' with: 1.

Now that we have a worksheet, we can work with cells and do the types of operations that we want to with Excel. First we will change the sheet name (the tab at the bottom).

pWorksheet putProperty: 'Name' value: 'MySheet'.

Notice that the tab name changed to MySheet.

Finally, let's put a text string into cell A1 (internally called 1,1).

First get the collection of cells.

pRange := pWorksheet getProperty: 'Cells'.

From this collection, select a single cell.

pCell := pRange getProperty: 'Item' with: 1 with: 1.

Now we can work with this cell. Set it's value to the string Smalltalk MT.

pCell putProperty: 'Value' value: 'Smalltalk MT'.

Let us also put the number 10 into Cell A2 (internally known as 2,1 i.e. row,col).

(pRange getProperty: 'Item' with: 2 with: 1)
	putProperty: 'Value' value: '10';
	Release.

This format fetches a cell, sets its value and releases the cell. It is very important to release any IDispatchs that we have created. COM keeps reference counts of all objects fetched and released and resources will not be freed up unless interfaces are released. So release everything we have used internally so far.

pCell Release.
pRange Release.
pWorksheet Release.
pSheets Release.
pWorkbook Release.
pWorkbooks Release.

To shutdown Excel, we need to call Quit. If you look at the pExcel IDispatch table, you will see the Quit is a FUNC. This requires us to invoke the call.

pExcel invoke: 'Quit'.

Excel will now pop up the save Dialog "Do you want to save the changes you made to 'Book1'?

You can avoid this dialog by setting the saved property before calling Quit.

pWorkbook putProperty: 'Saved' value: true.

Finally remember to release the instance of Excel. Quitting the instance DOES NOT release it. If you look at Task Manager you will still see Excel running.

count := pExcel Release.

If you examine count, its value should be zero. This means that all interfaces have been released and the instance of Excel will now shutdown (see in Task Manager).

If the count is NOT zero, you will need to go back and examine your code for missing release. You cannot just send multiple releases to pExcel, since this object is only tracking its own references.