COM and Excel examples

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 page 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

Here we will use a COM object called by Analytica's built in SpreadsheetOpen(...) function, and add a worksheet to it.

• First we need to get a COM Object that Analytica can work with...

Variable Workbook := spreadsheetopen("", showDialog: true)

The SpreadsheetOpen(..) function returns an Excel Workbook Object. This variable when evaluated will prompt you to select a spreadsheet file so just choose any Excel workbook you have laying around.

• In order to add a new worksheet, we need to get the Excel Worksheets COM object, which is a property of the Excel 'Workbook' COM object.

<code.:Variable Worksheets := Workbook -> Worksheets

(Note that we used the more concise code>' -> ' invoke method operator, We could have written the longer ComGetProperty(Workbook,"Worksheets"))

• The Worksheets COM object represents a collection of 'Worksheet' COM objects, it has a method 'Add' which, as expected, adds a new worksheet to the workbook.

Variable Add_a_worksheet := Worksheets -> Add()

The definition of this function is Worksheets->Add() ,Which is syntactic sugar for COMCallMethod(Worksheets,"Add")

The Add method returns the Worksheet com object which you can you use later. For example, you might want to rename the worksheet (I'm foreshadowing the next example.)