COM and Excel examples

In this example we use Analytica's COM Integration to make changes to Excel workbooks and worksheets.

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

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, a property of the Workbook Object which returns a Sheets COM object, 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.

Example 2: Renaming an Excel worksheet.

First we get a Worksheets COM object using the same calls as in the first Example. Then we rename the first worksheet.

To access one worksheet from the collection of Worksheet objects use the Item() property, with one required parameter - «Index».

The second step renames this worksheet using the Worksheet.Name property. There are no parameters, use the  := assignment operator to name it.

Variable First_Worksheet := Worksheets2 -> item(1)

Variable Rename_first_Sheet := First_Worksheet -> Name := Date_and_time

The Worksheets COM Object accepts an object qualifier, so in this case we can also get the first Worksheet's COM Object by passing (1) directly to the 'Worksheets' object, without calling the 'Item' Property.

Workbook2 -> Worksheets(1)

Example 3: Create a new Excel workbook. Add a range with a border around it and with colored cell interiors.

To create a new Excel workbook, we'll use 3 variables. Once this COM object is created it becomes the active workbook, and in Analytica it will return an 'ExcelWorkbook', which can be used by Analytica's built in spreadsheet functions.

First we need to call the Excel.Application COM object directly.

Then we need to get the Workbooks COM object, a collection of all open workbooks.

Finally, call the Workbooks.Add method to create the new workbook.

Variable Excel_Application := COMCreateObject("Excel.Application")

Variable Open_Excel_Workbooks := Excel_Application -> Workbooks

Variable Add_a_workbook := Open_Excel_Workbooks -> Add

So once we have a workbook the next step is to designate a range of cells we want to format.

First the worksheet. We use the ActiveSheet property of Workbook, which returns, as you would expect, the active sheet of the workbook. You could just as easily have used the calls in the previous example.

Then within this worksheet we designate the range of cells which we want to format by calling the Worksheet.Range property.

Variable ActiveSheet := Add_a_workbook -> ActiveSheet

Variable ActiveSheet__Range := ActiveSheet -> Range("B2:F6")

The Range Object has a method BorderAround which draws a border around a range of cells. We'll use it here to draw a red border with a dotted line style around the Range of cells.

Set the first parameter - «LineStyle» Variant - to '-4118' which is a dotted border.

We pad the second parameter «Weight» with Null. Otherwise Excel will throw an error.

The 3rd parameter is the «ColorIndex» which we'll set to 3, which on the Excel color Palette is Red.

The 4th Parameter - «Color Variant» - can be used instead, but you need to convert RGB to Excel color first... Excel Color in RGB

Variable BorderAround := ActiveSheet__Range -> BorderAround(-4118, null, 3)

The Range Object has an Range.Interior property which returns a Interior Object - the cell interior. So we need to get this Object to change the cell background color.

Then you can set the Interior.ColorIndex property. Here we set it to 19 which is a light yellow. Excel color Palette.

Variable Cell_interior := ActiveSheet__Range -> Interior

Variable Set_interior_color__ := Cell_Interior -> ColorIndex := 19

So once we have created these variables, we can evaluate the BorderAround and the Set_interior_color variables, then save the workbook using Analytica's SpreadsheetSave function. The sheet we worked with will now have the range with the border and color we set.

• We could have added any data we wanted to the cells in the range first with SpreadsheetSetRange function.

• This model contains a variable to close the workbook - only necessary with older Excel versions which will not close the Excel application automatically when an Analytica model is closed.

Variable Close_workbook := Add_a_workbook -> Close

Excel Color using RGB

MSDN reference Adding color to Excel
A page explaining Excel and RGB

The Interior Object has a Property Interior.Color which can be used to set the Cell interior color. BorderAround also has a Color variant.

• These need to be set using RGB values. We need to pass the color value to Excel as a decimal integer. Excel will assign this to a 4 byte Long Int with Red as the least significant byte. Essentially this means that the RGB hex values appear inverted.

• Basically you take the Hex numbers for the RGB values and invert them. So for an RGB of 204,238,255 - a light blue green - (CC,EE,FF in Hex) you would invert the 3 Hex numbers to get the hex number FFEECC.

Then convert this to decimal - 16772812., using for instance the Analytica Hex2dec function in the base conversion library.

Variable Set_interior_color := Cell_Interior -> Color := 16772812

{Or like this:}

{Variable Set_Interior_Color := Cell_interior -> Color := Hex2dec('FFEECC')}

Excel API errors

• If Excel detects an error in a COM access, it often gives you a rather uninformative message, "The notorious general Excel exception 0x800AE03EC occurred when evaluating obj->Range." Common reasons are:
  • Referring to a nonexistent worksheet or range name.
  • Cell or range address has bad syntax.
• If a range name specified resembles a cell address, for example A1 or XFD1048576, the message "The following error occurred when evaluating obj->Name := ... to set a COM object property : Invalid number of parameters."
• If a sheet name specified does not match Excel's specifications for sheet names (length of name, allowed characters, etc), the message "The following error occurred when evaluationg obj->Name := ... to set a COM object property: You typed an invalid name for a sheet or chart. Make sure that:
  • The name that you type does not exceed 31 characters.
  • The name does not contain any of the following characters: : \ / ? * [ or ]
  • You did not leave the name blank.

Excel API miscellaneous

• If you decide to use the Excel API 'Save' method instead of Analytica's built in SpreadsheetSave function I.E.

Workbook -> Save

You should know that Excel will save as a hidden sheet/Workbook. You can change this manually in Excel, or get around it by using COM calls in Analytica using the Windows -> 'visible' property. I.E.

COMGetProperty(Workbook, "Windows", 1)

COMPutProperty(windows1, "visible", True)

See Also

• COM Integration
• Excel spreadsheets read and write
• SpreadsheetOpen
• SpreadsheetSave
• COM Integration
• COM integration with Excel.ana