none
LINQ, VSTO and the ThisWorkbook.Sheets collection RRS feed

  • Question

  • Hello,

    As I have many sheets in an Excel workbook (VTSO project, Excel 2003),
    I fancied myself to select a subset of those sheets using LINQ.
    Here is the snippets that compiled correctly but did not execute correctly:

    IEnumerable<Worksheet> DbSheets = 
    
    from sh in Globals.ThisWorkbook.Sheets.Cast<Worksheet>() 
    where sh.Name.EndsWith("Db") 
    select sh;
    foreach (Worksheet sh in DbSheets)
    {
      ...
    }
    

    And this is the error message I have catched:

        Unable to cast COM object of type 'System.__ComObject' 
        to interface type 'System.Collections.IEnumerable'. 
        This operation failed because the QueryInterface call
        on the COM component for the interface with IID 
        '{496B0ABE-CDEE-11D3-88E8-00902754C43A}' failed 
        due to the following error: 'No such interface 
        supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE))' 
        and the COM component does not support 
        IDispatch::Invoke calls for DISPID_NEWENUM.

    Does that mean that somehow I will never be able to perform this LINQ query on the Sheet collection?
    Or does that mean that I need a bit more expertise?

    Thanks for your suggestions,

    Michel

    Friday, June 25, 2010 8:44 AM

Answers

All replies

  • Hi Michel,

    Glad to see you again!

    I think the issue here is that we cannot cast Globals.ThisWorkbook.Sheets into IEnumerable<Worksheet>.   Please make sure the namespace of Worksheet is correct, Microsoft.Office.Interop.Excel.Worksheet. 

    I am not an expert of VSTO.  However, if the worksheet collection can be in type of IEnumerable<Worksheet>, we can use LINQ to Objects on it.   I would recommend you create List<Worksheet> and then put each Worksheet in the Globals.ThisWorkbook.Sheets into the List<Worksheet>:

    ==================================================================

    List<Microsoft.Office.Interop.Excel.Worksheet> myList = new List<Microsoft.Office.Interop.Excel.Worksheet>();

    foreach (Microsoft.Office.Interop.Excel.Worksheet ws in Globals.ThisWorkbook.Sheets)
    {
        myList.Add(ws);
    }

    var DbSheets = from sh in myList
                                where sh.Name.EndsWith(“Db”)
                                select sh;

    ==================================================================

    Please try my suggestion and tell me whether it solves the problem.   Feel free to let me know if you have any questions.  

    Have a nice weekend!


    Lingzhi Sun
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact
    msdnmg@microsoft.com. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Saturday, June 26, 2010 2:58 AM
    Moderator
  • Hello Linqzhi,

    Your suggestion works perfectly.
    I also tried your suggestion slightly modified by repacing  "List<Worksheet>"  with  "ArrayList".
    This generates a compile-time error on the "allSheets" variable in bold here below: 

        ArrayList allSheets = new ArrayList();
        foreach (Worksheet sh in Globals.ThisWorkbook.Sheets) allSheets.Add(sh);
        var DbSheets =  from sh in allSheets
                                 where sh.Name.EndsWith("Db")
                                 select sh;

    This is the error message:  

         Could not find an implementation of the query pattern for source type 'System.Collections.ArrayList'. 
         'Where' not found.  Consider explicitly specifying the type of the range variable 'sh'.

    I was not able to put a cast somewhere to arrange that.
    Do you think there is a way to have that working also with anArrayList? (just by curiosity)

    Thanks a lot,

    Michel

    By the way: Is there a way to copy and paste the error codes displayed in a tooltip in Visual Studio?

    Saturday, June 26, 2010 7:13 PM
  • Hi Michel,

     

    I would recommend you still use List<> instead of ArrayList.  J   Because ArrayList does not implement IEnumerable<> interface which supports LINQ to Objects queries.   

     

    We can put any objects into ArrayList so it is not type safe.   To use LINQ queries, we need to specify the type information of the generic list.   To make ArrayList work, we can call .Cast<> to make ArrayList into an IEnumerable<> list.  

     

    For the question about copying the error in tooltip, I am afraid tooltip is not copyable, http://social.msdn.microsoft.com/Forums/en/vside2008/thread/c508bc09-cae5-4a95-9680-bf77eef9c294.  

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, June 28, 2010 9:05 AM
    Moderator