Overly intimate with the Excel OM

I’d say I know my way around the Excel object model pretty well, although, of course like everyone there are plenty of gaps.

The more I work with C# and its anal type pedantic-ness, the more of those gaps I start to see.

Someone emailed me ages ago to point out that the Excel state management module that is available here treats the calc mode as a long, and it could be/should of course be an xl.XlCalculation (!).

Here are some other nuggets from a recent project:

FormulaDestStart = (xl.Range)wsDest.Cells[((xl.Range)DestDataRange.Cells[1, 1]).Row, FormulaDestCol];

m_xlapp.CutCopyMode = (xl.XlCutCopyMode)0;

refersTo.get_Address(oo, oo, xl.XlReferenceStyle.xlA1, oo, oo));

((xl.Worksheet)m_DestWorkbook.Worksheets.get_Item(c_ControllerName)).get_Range("Acc...

m_DestWorkbook.SaveAs(fsn, xl.XlFileFormat.xlExcel12, oo, oo, true, oo,

xl.XlSaveAsAccessMode.xlNoChange

I know some of my gaps are based on my mainly trading off Excel 97 knowledge, typeness has tightened in each version.  But some of these are just a bit on the excessive side for me.

I do in fact plan to fully refresh my library for Office 2010, not because it is a huge leap, but because I think its a good solid release and one I suspect corporates will consider moving to. Not sure yet if it is like 2002 (xp) or 2003 (classique). Of course 2003 is the main corporate staple at the moment. I think that will change in the next 18 months.

The get_address is a bit of the PITA for debugging I find, do you?

Have you come across any overly verbose/precise references like these? faves?

cheers

simon

Advertisements

3 Responses to “Overly intimate with the Excel OM”

  1. Harlan Grove Says:

    I’ve never used C#. Does it lack C’s #define? If it has that, why not roll your own typed symbolic constants, e.g.,

    #define XLCutCopyMode_FALSE ((xl.XlCutCopyMode) 0)

    ? ‘Course I’d shorten the name further myself.

    Does C# not provide function overloading? If it does, couldn’t you roll your own functions to accept convenient data types?

    Or and I way off base thinking C# and C++ are closely related?

  2. Simon Says:

    Harlan, yes you can overload, infact the VSTO team released a powertoy with many overloads for the Excel OM. And yes you can #define too.
    I kinda think Ms should be making their stuff easily accessble rather than expect each of us to so this stuff ourselves. which I think they have in VSTO 2010/.net4.0.

    Especially if they are trying to attract the VBA crowd. If they are that is…

  3. Harlan Grove Says:

    OK, if there’s overloading, then to me it seems obvious that VSTO shouldn’t have any issues with expected type conversions in arguments to OM method and property calls. Either that or have ‘Intellisence’ offer type casting syntax in more common situations.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: