View source for COM and Excel examples

You can download an example model here: [[Media:COM integration with Excel.ana | 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.

[https://msdn.microsoft.com/en-us/library/office/ff194565.aspx 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.
 <code>Variable Workbook := [[spreadsheetopen]]("", showDialog: true)
 Variable Worksheets := Workbook -> Worksheets
 Variable Add_a_worksheet := Worksheets -> Add()</code>

:The [[SpreadsheetOpen]](..) function returns an Excel [https://msdn.microsoft.com/en-us/library/office/ff835568.aspx Workbook] COM Object. 
:The next variable calls the the [https://msdn.microsoft.com/EN-US/library/office/ff835542.aspx Worksheets] COM object, a property of the [https://msdn.microsoft.com/en-us/library/office/ff835568.aspx Workbook] Object which returns a [https://msdn.microsoft.com/EN-US/library/office/ff193217.aspx Sheets] COM object, a collection of all the worksheets and charts in the Workbook. 
:The 3rd variable calls the [https://msdn.microsoft.com/EN-US/library/office/ff839847.aspx Sheets.Add()] method, and adds a new worksheet to the Excel workbook.

:(Note the use of the <code>' -> '</code> [[COM_Integration#The_Invoke_Method_operator | invoke method operator]], introduced in [[Analytica 4.6]].

==Example 2:  Renaming an Excel worksheet.==
First we get a [https://msdn.microsoft.com/EN-US/library/office/ff835542.aspx 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 [https://msdn.microsoft.com/en-us/library/office/ff194464.aspx Worksheet] objects use the [https://msdn.microsoft.com/EN-US/library/office/ff838615.aspx Item()] property, with one required parameter - ''Index''. 
:The second step renames this worksheet using the [https://msdn.microsoft.com/EN-US/library/office/ff196974.aspx Worksheet.Name] property. There are no parameters, use the <code> := </code> assignment operator to name it.

 <code>Variable First_Worksheet := Worksheets2 -> item(1)
 Variable Rename_first_Sheet := First_Worksheet->Name := Date_and_time</code>

: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.

 <code>Workbook2 -> Worksheets(1)</code>

==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 [https://msdn.microsoft.com/EN-US/library/office/ff194565.aspx Excel.Application] COM object directly.
:Then we need to get the [https://msdn.microsoft.com/EN-US/library/office/ff820765.aspx Workbooks] COM object, a collection of all open workbooks.
:Finally, call the [https://msdn.microsoft.com/EN-US/library/office/ff840478.aspx Workbooks.Add] method to create the new workbook. 
 <code>Variable Excel_Application := COMCreateObject("Excel.Application")
 Variable Open_Excel_Workbooks := Excel_Application->Workbooks
 Variable Add_a_workbook := Open_Excel_Workbooks -> Add</code>

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 [https://msdn.microsoft.com/EN-US/library/office/ff836512.aspx Worksheet.Range] property.
 
 <code>Variable ActiveSheet := Add_a_workbook->ActiveSheet
 Variable ActiveSheet__Range := ActiveSheet->Range("B2:F6")</code>

The Range Object has a method [https://msdn.microsoft.com/EN-US/library/office/ff197210.aspx 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 [https://msdn.microsoft.com/en-us/library/cc296089%28v=office.12%29.aspx#xlDiscoveringColorIndex_ColorIndexProperty Excel color Palette] is Red.
:The 4th Parameter - ''Color Variant'' - can be used instead, but you need to convert RGB to Excel color first...[[Working with Excel Worksheets using the Excel 2013 Object Model.#Excel_Color_using_RGB | Excel Color in RGB]]

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

The Range Object has an Range.Interior property which returns a [https://msdn.microsoft.com/EN-US/library/office/ff839659.aspx 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. [https://msdn.microsoft.com/en-us/library/cc296089%28v=office.12%29.aspx#xlDiscoveringColorIndex_ColorIndexProperty Excel color Palette].

 <code>Variable Cell_interior := ActiveSheet__Range->Interior
 Variable Set_interior_color__ := Cell_Interior->ColorIndex := 19</code>

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.

[[File:New wb borderaround and interior.png]]

:*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.

 <code>Variable Close_workbook :=Add_a_workbook->Close</code>

==Excel Color using RGB==
[https://msdn.microsoft.com/en-us/library/cc296089%28v=office.12%29.aspx MSDN reference Adding color to Excel]<br\>
[http://stackoverflow.com/questions/24132665/ A page explaining Excel and RGB]

The Interior Object has a Property [https://msdn.microsoft.com/EN-US/library/office/ff840499.aspx 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 <code>204,238,255</code> - a light blue green - (<code>CC,EE,FF</code> in Hex) you would invert the 3 Hex numbers to get the hex number <code>FFEECC</code>.

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

 <code>Variable Set_interior_color := Cell_Interior->Color:=16772812
 {Or like this:}
 {Variable Set_Interior_Color := Cell_interior->Color:=Hex2dec('FFEECC')}</code>

==Excel API miscellaneous==
*If you decide to use the Excel API 'Save' method instead of Analytica's built in SpreadsheetSave function I.E.
 <code>Workbook->Save</code>

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.

 <code>COMGetProperty(Workbook,"Windows",1)
 COMPutProperty(windows1,"visible",True)</code>