COM and Excel examples

Under construction

You can download an example model here: COM integration with Excel.ana

In this example we use Analytica's COM Integration (introduced in Analytica 4.6) to make changes to Excel workbooks and worksheets.

(Generally it's simpler to use Analytica's built in Functions to Write Data to Excel Worksheets and Functions To Read Excel Worksheets to integrate with Excel. This model shows some examples that are not currently available in these functions.)

Microsoft Development center reference for the Excel Application Object contains documentation for all the objects, properties, methods, and events contained in the Excel object model.

Example 1: Add a worksheet to an Excel workbook

We can use Analytica's built in SpreadsheetOpen function to call a Workbook COM object, and then use the COM Integration functionality to add a worksheet to it .

This is straightforward. We create 3 variables.

Variable Workbook := spreadsheetopen("", showDialog: true)
Variable Worksheets := Workbook -> Worksheets
Variable Add_a_worksheet := Worksheets -> Add()

The SpreadsheetOpen(..) function returns an Excel Workbook COM Object.

The next variable calls the the Worksheets COM object, which is a property of the Workbook Object and returns a Sheets COM object, which is a collection of all the worksheets and charts in the Workbook.

The 3rd variable calls the Sheets.Add(), method,and adds a new worksheet to the Excel workbook.

(Note the use of the ' -> ' invoke method operator, introduced in Analytica 4.6.