Difference between revisions of "COM and Excel examples"
m
 
(33 intermediate revisions by 5 users not shown)
Line 1: Line 1:
−
In this example we use Analytica's [[COM Integration]] (introduced in [[Analytica 4.6]]) to make changes to Excel Workbooks and worksheets.
+
[[Category: COM integration]]
 +
[[Category:Excel to Analytica mappings]]
  
−
(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.)
+
__TOC__
 +
 
 +
 
 +
In this example we use Analytica's [[COM Integration]] to make changes to Excel workbooks and worksheets.
 +
 
 +
You can download an example model here: [[Media:COM integration with Excel.ana | COM integration with Excel.ana]]
  
 
[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.   
 
[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)</code>
 +
:<code>Variable Worksheets := Workbook -> Worksheets</code>
 +
:<code>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)</code>
 +
:<code> 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")</code>
 +
:<code>Variable Open_Excel_Workbooks := Excel_Application -> Workbooks</code>
 +
:<code>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.
  
−
==Example 1: Add a worksheet to an Excel workbook==
+
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</code>
 +
:<code>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</code>
 +
:<code>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.
  
−
Here we will use a COM object called by Analytica's built in SpreadsheetOpen(...) function, and add a worksheet to it.
+
:<code>Variable Close_workbook := Add_a_workbook -> Close</code>
  
−
*First we need to get a COM Object that Analytica can work with...  
+
==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]
  
−
:<code>Variable Workbook := spreadsheetopen("", showDialog: true)</code>
+
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.
  
−
:The [[SpreadsheetOpen]](..) function returns an Excel [https://msdn.microsoft.com/en-us/library/office/ff835568.aspx Workbook] Object. This variable when evaluated will prompt you to select a spreadsheet file so just choose any Excel workbook you have laying around.
+
*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.
  
−
*In order to add a new worksheet, we need to get the Excel [https://msdn.microsoft.com/EN-US/library/office/ff835542.aspx Worksheets] COM object, which is a property of the Excel 'Workbook' COM object.
+
*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>.
  
−
:<code.:Variable Worksheets := Workbook -> Worksheets</code>
+
Then convert this to decimal - 16772812., using for instance the Analytica Hex2dec function in the base conversion library.
  
−
(Note that we used the more concise code>' -> '</code> invoke method operator, We could have written the longer <code>ComGetProperty(Workbook,"Worksheets")</code>)
+
:<code>Variable Set_interior_color := Cell_Interior -> Color := 16772812</code>
 +
{Or like this:}
 +
:<code>{Variable Set_Interior_Color := Cell_interior -> Color := Hex2dec('FFEECC')}</code>
  
−
*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.
+
==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.
  
−
:<code>Variable Add_a_worksheet := Worksheets -> Add()</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>
  
−
The definition of this function is <code> Worksheets->Add() </code> ,Which is syntactic sugar for <code>COMCallMethod(Worksheets,"Add") </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.
  
−
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.)
+
:<code>COMGetProperty(Workbook, "Windows", 1)</code>
 +
:<code>COMPutProperty(windows1, "visible", True)</code>
  
−
==Example 2: Renaming an Excel worksheet.==
+
==See Also==
 +
* [[COM Integration]]
 +
* [[Excel spreadsheets read and write]]
 +
* [[SpreadsheetOpen]]
 +
* [[SpreadsheetSave]]
 +
* [[COM Integration]]
 +
* [[Media:COM integration with Excel.ana | COM integration with Excel.ana]]

Latest revision as of 00:37, 25 May 2024



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.

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

Comments


You are not allowed to post comments.