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

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 *