none
Excel 2010 COM interface returns different class code for sheets & ranges

    Question

  • I am accessing Excel 2010 via COM from Matlab.  In Excel 2007, I use to be able to query the interface for the type of Excel object:

    if regexp( class(shtOrRg), '\._Worksheet' )
         sht = shtOrRg;
      elseif regexp( class(shtOrRg), '\.Range' )
         sht = shtOrRg.Parent;
      else
         error([ mfilename ': shtOrRg must be worksheet or range.' ]);
      end % if

    As might be inferrable from the code (even from non-Matlab users), this code queries the COM interface for the class of the object to which a handle shtOrRg points.  If it ends in '._Worksheet', it is a worksheet and if it ends in '.Range', it is a range.  Otherwise, an error exception is thrown.

    When I tried with with Excel 2010 on a colleague's laptop, it broke.  The "class" function returns rather cryptic names for sheet or range handles.  I'm sure that I can hard code the new return codes into coder-friendly global constants and test for them instead, but I was wondering if there was a less abhorrent way to do such a test.  My global list of Excel constants is getting quite long, and I don't want more stuff that I have to change based on the laptop and Office version that I happen to have access to.
    Friday, June 22, 2012 9:31 PM

All replies

  • Judging from the lack of response, I'm assuming that COM interfacing may not be the most popular usage of Matlab.  I was wondering of the following related question might be answerable.  Of Matlab and Excel, which app is actually responsible for the code that is returned from the "class" function when applied to a sheet or range handle?  That would help me determine which forum to follow up on or check back on.

    Thanks.
    Monday, June 25, 2012 2:15 AM
  • Hi,

    I want to confirm that where you write the code? In Excel VBE?

    And is there any error messages return?

    And based on my research, the “regexp” means Regular Expressions, and it is an object.

    When use the Regular Expressions object, it need to be created first.

    Please refer to the link:

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


    Jaynet Zhang

    TechNet Community Support

    Monday, June 25, 2012 6:56 AM
  • I'm accessing Excel through COM.  I am writing the code in a Matlab environment.  I don't know whether Matlab or Excel is responsible for the code that comes back when I invoke class(SheetOrRangeHandle).  The code distinguishes between object types (or classes).  When I used Excel 2007 on my own laptop, the code was a string that ended in "Worksheet" or "Range", so it's easy to write readable source code that recognizes the type of object handle.  Since using Excel 2010 on a colleague's laptop, I found that the return code was very cryptic.  That is a dual penalty.  Not only is the return code different from one Excel version to the next, but the return code from Excel 2010 is not very meaningful.

    Hopefully, there is a more robust and and less cryptic way to test for the different types of objects.

    Tuesday, June 26, 2012 1:23 AM
  • Hi Franky,

    Now that this is a development issue, so I tried to consult this question with our development team, following is the response I got:

    --

    It’s based on my experience that this issue is an Matlab issue. It’s out of the support range of Our scope. However I never see the scenario on my side as normal scenario. I think it might make more sense to ask OP consult Matlab for such issue.

    --

    Also, by a quick search, I didn't find such similar issues.

    So, I'd suggest to post your question to the Matlab community: http://www.mathworks.com/matlabcentral/

    And, if you need more help on Office development topic, please visit our Development forum category: http://social.msdn.microsoft.com/Forums/en-US/category/officedev


    Max Meng

    TechNet Community Support

    Tuesday, June 26, 2012 6:25 AM
  • Thanks, Max.  Just sent the test files to TMW on Friday (though I expect a prompt for the license, which I forgot to include).
    Monday, July 02, 2012 5:24 PM