Excel VSTO C# - Creating a ListObject's VSTO counterpart breaks renaming

user_6115428 0
Viewed 346

I'm writing a VSTO add-in for Excel using C# and need to tie meta data to tables created in a worksheet which cannot be exposed to users or be copied when tables are duplicated in the worksheet. For that I'm using ListObject's Tag property which works well and seems to be intended for this use case. In order to set the Tag property I create the ListObject's VSTO Object as the property is not available in the interop object:

foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets)
{
    foreach (Microsoft.Office.Interop.Excel.ListObject table in worksheet.ListObjects)
    {
        Microsoft.Office.Tools.Excel.ListObject vstoTable = Globals.Factory.GetVstoObject(table);
        vstoTable.Tag = new Tag { Identifier = 123 };
    }
}

As soon as I create the VSTO object for the ListObject however, I cannot change the table's name any longer. I can still edit it in the table options UI but saving the workbook will automatically revert the name to whatever it was at the time the VSTO object was created. I tried disposing the VSTO object to no avail. The issue persists even if I do not set the Tag, just creating the VSTO object seems to be enough to trigger this. Interestingly enough, I am also creating the VSTO object counterparts for worksheets and set their Tag property but I can still rename those even after the objects have been created and tags have been set.

To verify that none of my other code impacts this I created a fresh add-in project with ONLY the above code snippet put on a ribbon button and the same issue occurs. I also tried using several other properties available in interop to avoid creating the VSTO object but they are all either exposed to users, get copied along with the table or both.

Clearly I am either using the objects incorrectly or this is an issue within Excel. Does anyone know how I can set the Tag without losing the ability to rename the table or if there is any other approach I could use to attach meta data to tables?

office-addins add-in vsto excel c#
1 Answers

You may want to try deleting and re-creating the list object with the name that you want each time like this extension method shows. This may not work in your solution or you may have to modify the arguments to create and old list object with a "new" name. I data bind my list object each time it is re-created so this works for my situation.

    /// <summary>
    /// Creates a VSTO list object by searching for a native list object by name
    /// If the list object is found it will cast it to a 
    /// VSTO list object and remove it from the worksheet
    /// Once removed (or not found) it will create a VSTO list object
    /// and add it to the worksheet
    /// </summary>
    /// <param name="worksheet"> The worksheet to add the list object to</param>
    /// <param name="name"> The name of the list object to re-create </param>
    /// <returns> The VSTO list object </returns>
    public static ExcelVSTO.ListObject RecreateVSTOListObject(this ExcelVSTO.Worksheet worksheet, string name)
    {
        Excel.ListObject listObject = Globals.ThisAddIn.GetListObjectByName(worksheet, name);

        if (listObject != null)
        {
            Microsoft.Office.Tools.Excel.ListObject vstoListObject = Globals.Factory.GetVstoObject(listObject);
            worksheet.Controls.Remove(name);
        }

        return worksheet.Controls.AddListObject(Globals.ThisAddIn.Application.Selection, name);
    }
user_44907960
Related