none
In need of an advice (VSTO 3.0 and Excel 2007) RRS feed

  • Question

  • Hello,

     

    I am in need of an idea regarding a project I am starting, an Add-In for Excel 2007 using VSTO 3.0 and using C# language. I am searching for a way to store in the document some object. My objects have Data(key,value) and an UUID.

     

    My goal is to display the Data.Values in the document, and add some background data to each Excel Cell so I know the connection and then close the document. Upon opening the document, the data stored in the background of the cell would suffice me to recognize the Data.Key and UUID of the element in comes from.

     

    I was thinking of using some kind of XML mapping, or using the Name property of the cells. Any advices?

     

    Best regards,

    Silviu.


    http://www.rosoftlab.net/
    Wednesday, July 20, 2011 7:51 AM

Answers

  • Hello,

    Found the solution for my problem.

    To set the name to each cell:

            foreach (var cell in node.Element.Cells)
            {
              (activeCell[1, i] as Microsoft.Office.Interop.Excel.Range).Value2 = cell.Key;
              (activeCell[2, i] as Microsoft.Office.Interop.Excel.Range).Value2 = cell.Value.Value;
              (activeCell[2, i] as Microsoft.Office.Interop.Excel.Range).Name = NameConverter(cell.Key, node.Element.UUID);
            }

    To get the name:
    string name = ConvertBack(((Excel.Name)Target.Name).Name);
    


    This works great, and the name are saved after leaving the document.

     

    The problem now is that, each and every time I click on a cell that I have not set a name to it, I get an exception: Error 'Exception from HResult:0x800A03EC. Does someone know the cause of this exception? I am running on a 32bits Windows7 using Visual Studio 2010 and Office 2007.

     

    Best regards,

    Silviu.


    http://www.rosoftlab.net/
    Wednesday, July 20, 2011 12:03 PM

All replies

  • Hi Silviu

    For storing the data, I suggest a CustomXMLPart.

    Unfortunately, Excel has no way to directly link a cell/range in a workbook to an CXP, so yes, I'd consider using Named Ranges and storing that information with the data. I'm not sure XML Mapping would work (but then, I don't know that much about XML Mapping in Excel...)


    Cindy Meister, VSTO/Word MVP
    Wednesday, July 20, 2011 10:02 AM
    Moderator
  • Hello,

    Found the solution for my problem.

    To set the name to each cell:

            foreach (var cell in node.Element.Cells)
            {
              (activeCell[1, i] as Microsoft.Office.Interop.Excel.Range).Value2 = cell.Key;
              (activeCell[2, i] as Microsoft.Office.Interop.Excel.Range).Value2 = cell.Value.Value;
              (activeCell[2, i] as Microsoft.Office.Interop.Excel.Range).Name = NameConverter(cell.Key, node.Element.UUID);
            }

    To get the name:
    string name = ConvertBack(((Excel.Name)Target.Name).Name);
    


    This works great, and the name are saved after leaving the document.

     

    The problem now is that, each and every time I click on a cell that I have not set a name to it, I get an exception: Error 'Exception from HResult:0x800A03EC. Does someone know the cause of this exception? I am running on a 32bits Windows7 using Visual Studio 2010 and Office 2007.

     

    Best regards,

    Silviu.


    http://www.rosoftlab.net/
    Wednesday, July 20, 2011 12:03 PM
  • I assume that you're using some kind of event, and the error is occurring in the event? So you need to track down what line of code in the event that's causing the problem...
    Cindy Meister, VSTO/Word MVP
    Thursday, July 21, 2011 11:32 AM
    Moderator
  • Hello Cindy,

     

    This is the line and the event stripped.

        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
          this.Application.SheetSelectionChange += new Excel.AppEvents_SheetSelectionChangeEventHandler(Application_SheetSelectionChange);
        }
    
        void Application_SheetSelectionChange(object Sh, Excel.Range Target)
        {
          string name = ((Excel.Name)Target.Name).Name;
        }
    


    When moving the cursor in the document, if the Com exception are activated, it will throw an exception everytime, if a name is not set manually on the cells you place the cursor.

     

    Best regards,

    Silviu.

     


    http://www.rosoftlab.net/
    Thursday, July 21, 2011 11:40 AM
  • Hi Silviu

    The error makes sense: you're demanding a Name and there isn't one.

    Is "Excel" (as in Excel.Name) in your using statements set to the Interop or the Tools namespace? I'm guessing the Interop?

    In that case, it would probably be a good idea to ask the application specialists how to check whether a Range has been assigned a name in order to avoid getting an error. Those folks are in the Excel for Developers forum.

    I'd split this off, as it's a different discussion any way, and move it there. But then I'd lose the "Answer" message. So I'm going to mark that as the Answer in order to close this thread and I urge you to re-post this question in the Excel for Developers forum.


    Cindy Meister, VSTO/Word MVP
    Thursday, July 21, 2011 11:54 AM
    Moderator