COM/ActiveX tips & tricks

Matlab’s COM/ActiveX interface has been supported and well-documented for many releases. However, there are still some aspects that are either not detailed, or that escape the casual documentation reader.

Accessing collection items

COM collections is a COM interface for sets of similar objects such as the worksheets in an Excel file or the images in a PowerPoint document. The items in a collection can be accessed using a numeric index (starting at 1) or the item’s string name.

The “normal” way to access collection items is using the Item() method that accepts a numeric index or the item’s name (a string).

Since collections are so common, Microsoft devised a short-cut of passing the parameter directly to the collection. For example, in our Excel VB code, instead of using Worksheets.Item(2) or Worksheets.Item(‘Sheet2′), we could write Worksheets(2) or Worksheets(‘Sheet2′). This shortcut is so common that the “normal” way of using Item() is rarely seen.

Unfortunately, Matlab’s implementation of the COM interface does not recognize this shortcut. Instead, we must use the more verbose way of using the Item():

% Invalid - shortcut is not recognized by Matlab
>> hSheet = hWorkbook.Worksheets(2);
??? Index exceeds matrix dimensions
 
% Valid
>> hSheet = hWorkbook.Worksheets.Item(2);
>> hSheet = hWorkbook.Worksheets.Item('Sheet2')
hSheet =
	Interface.Interface.Microsoft_Excel_11.0_Object_Library._Worksheet

Note that the dot-notation used above only works on recent Matlab 7 releases. Earlier releases (for example, Matlab 6.0 R12) have bugs that prevent it from functioning properly. The workaround is to use the following even-more-verbose way, which work on all Matlab releases:

hSheet = invoke(get(hWorkbook,'Worksheets'),'Item',2);

Matlab’s documentation actually has a short section describing the valid way to access COM collection. IMHO, a special warning about the invalid but widely-used short-cut would have been useful. In any case, the issue of accessing collection items has often appeared on CSSM (for example, here and here), so I guess many programmers have overlooked this.

Using enumerated values

When setting COM property values, Matlab supports some enumerated (constant) values but not all (read here). In practice, this can be very frustrating since the VB code and documentation almost always refers to the enumerated values only. Without the ability to set enumeration values, some properties become unusable in Matlab.

Well, not really. There’s a workaround: Since every enumerated COM value hides a numeric value, we can pass the numeric values rather than the enumerated (string) value when setting such properties. The numeric values are seldom documented, but can often be easily found online (use Google!). Quite often, they appear in C header-files that #define the enumerated values as pre-processor items with the required numeric value. For example, the numeric value for xlXYScatterLines is easily found to be 74.

Some of the enumerated constants are harder to find in this manner. You can use one of the following resources to search for your requested constant: Excel, PowerPoint (or here), OLE/Office (includes Word, Access and Internet Explorer), and an even larger list.

Again, old Matlab versions have problems understanding string enumerations, but the numeric values are always accepted and so are backward-compatible. If your application needs to support old Matlab versions, always use the numeric values (add a comment with the enumerated name, for maintainability).

Office 2010

Microsoft Office 2010 has apparently changed its COM interface, so accessing it from Matlab cannot easily be done. Luckily, Samuel Foucher has posted a workaround to this problem on CSSM yesterday. The trick is basically to have both an older Office and Office 2010 installed at the same time. As Samuel notes, “This is far from an optimal solution but it seems to work so far“.

Related posts:

  1. Controlling plot data-tips Data-tips are an extremely useful plotting tool that can easily be controlled programmatically....
  2. Running VB code in Matlab Matlab does not natively enable running VB code, but a nice trick enables us to do just that...
  3. Modifying default toolbar/menubar actions The default Matlab figure toolbar and menu actions can easily be modified using simple pure-Matlab code. This article explains how....
  4. Spy Easter egg The built-in Matlab function spy has an undocumented feature (Easter egg) when it is called with no input arguments....
  5. datestr performance Caching is a simple and very effective means to improve code performance, as demonstrated for the datestr function....
  6. xlswrite for Mac, Linux Several Matlab utilities enable reading/writing spreadsheet data (including XLS files) in Macs, Linux. ...

Categories: Low risk of breaking in future versions, Stock Matlab function

Tags: , ,

Bookmark and SharePrint Print

One Response to COM/ActiveX tips & tricks

  1. Jason says:

    Hi Yair,

    Two comments here. First, there actually is a way to use the VB syntax via the verbose get and set. To use the context of your example:

    hSheet = get(hWorkbook,'Worksheets',2);

    Secondly, I have found the Microsoft developer resources pretty useful for finding enumerated values. Most of the time, they will list the numeric value next to the enumerated name. It’s pretty well laid out, except for using links instead of a tree view. Here is the link to the one for Excel:

    http://msdn.microsoft.com/en-us/library/bb149081.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *

*

<pre lang="matlab">
a = magic(3);
sum(a)
</pre>