Boo - Excel Automation, trouble selecting ranges

Question

I'm investigating Boo and thought it would be a useful exercise to try converting a couple of venerable VB Scripts that automate Excel (2007, in this instance). A lot of things seem to translate very easily, however I'm having a huge amount of trouble selecting ranges - whenever I try to get or set them I get a TargetInvocationException Member not found.

Here's a (cut down) example I've run in booish:

def CreateInstance(progid):
    type = System.Type.GetTypeFromProgID(progid)    
    return type()   

xl as duck = CreateInstance("Excel.Application")
xl.Visible = true
xl.Workbooks.Add

sht as duck = xl.ActiveSheet
#Next line throws exception
rng as duck = sht.Range("A1")

Certain things work fine, such as setting the Name property of the sheet and so on, but how do I work with ranges? Are there some special methods that VB hides that I'd need to call, and if so how would I go about finding those out?

Cheers,

Lenny.

Answer 1

Range is actually a property, and it's a somewhat special property in that it works as an Indexer, which means that it has array- or dictionary-like semantics. In most languages, that means that you'd access sht.Range["A1"]. That is syntactic sugar, and really it's accessed just like any other method, namely:

sht.get_Range("A1",System.Reflection.Missing.Method)

I attempted to use Boo, Ruby and IronRuby to repeat your code, using both the syntactic sugar style and the explicit method call. In IronRuby, I can get it to work flawlessly, but only in the 32-bit interpreter. In regular Ruby, which is a 32-bit app on my configuration, it also worked fine. In the 64-bit interpreter, the Range property was never resolved correctly.

So that led me to suspect that the Boo Interactive Shell was running in 64-bit mode and that interop was failing because of that. Unfortunately, the same issues reproduced after setting my local Boo binaries to run in 32-bit mode using CORFLAGS.exe, so I don't think that's the real issue.

What did work, though, was to explicitly import the Excel Dotnet Interop Library as well as the Interop services namespaces, like so:

import Microsoft.Office.Interop.Excel
import System.Runtime.InteropServices

xl_type=typeof(Application).GetCustomAttributes(typeof(CoClassAttribute),true)[0].CoClass
xl=xl_type()
xl.Visible=true
xl.Workbooks.Add

Then:

xl.Range["A1","A2"].Value=12
xl.Range["A1",System.Type.Missing].Value="Alpha"
(xl.ActiveSheet as Worksheet).Range["A1","A2"].Value2='Whatever'

All of these work, but they essentially require you to give up the "Scriptiness" you're used to from late-binding (which is what your duck typing is doing).

One difference from VB/VBScript that is true for most languages (other than C# 4.0) is that, generally, optional parameters are not handled transparently, so you'd need to look at the API more carefully when you deal with methods that support optional parameters (replacing them with System.Type.Missing or the System.Reflection equivalent). You'd find this through the Excel interop docs, although you can probably use reflection to identify parameters marked as optional if you find that easier than looking it up.

Because Ruby has a reasonable solution for late binding these objects, I suspect there's a missing feature (or bug) in COM interop scenarios in Boo.

Edited to add: Sam Ng writes about indexed property support in C# 4.0; the issues described in his post likely apply to Boo, as well.