none
The control cannot be added because a control with the name <xxxx> already exists in the Controls collection - dynamic ListObject RRS feed

  • Question

  • I have an Excel VSTO customisation where I create a dynamic ListObject (Excel Table).

    Users can then click a button to refresh the data.  The first thing I do then is this:

     

    foreach (Excel.ListObject l in ws.ListObjects)

        l.Delete();

    Which seems to work as the cells in the table are all cleared down and I can no longer see any reference to my Table in Excel.

    However, when I try to create a new ListObject with the same name then I get the error shown about the control already existing in the Controls Collection.  If I check on MSDN then I can see this:

    "Deletes a dynamically created ListObject control, clears the cell data from the worksheet, and removes the control from the ControlCollection."

    So it appears as if it is doing the first two things in that list but not the third?

    Tuesday, January 24, 2012 4:58 PM

Answers

  • Okay, just for completeness this is the full solution for getting back to an empty worksheet after adding dynamic Excel Tables (ListObjects):

                //Attach to the active worksheet
                Worksheet ws = (Globals.Factory.GetVstoObject((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet));

                //Remove ANY existing ListObjects before we clear down or they are left partially removed
                foreach (Excel.ListObject list in ws.ListObjects)
                {
                    ws.Controls.Remove(list.Name);
                    //list.Delete(); -- this is busted
                }
                Application.DoEvents();

                //Clear down anything else in the worksheet
                ws.Cells.ClearContents();
                ws.Cells.ClearComments();
                ws.Cells.ClearFormats();

    Tuesday, January 24, 2012 5:20 PM

All replies

  • I did a little extra checking.  If I call the Delete and then check this:

    ?ws.Controls["MyTable"].ToString()

    (Where ws is the Excel.Worksheet object.)  Then I get the following results:

    Microsoft.Office.Tools.Excel.ListObjectImpl

    Even though the ListObject is supposedly deleted!

     

     

    Tuesday, January 24, 2012 5:06 PM
  • ...and I now have a workaround.

    If I call this:

    ws.Controls.Remove("MyTable")

    before I delete the ListObject then it starts working again!

    Tuesday, January 24, 2012 5:11 PM
  • Okay, just for completeness this is the full solution for getting back to an empty worksheet after adding dynamic Excel Tables (ListObjects):

                //Attach to the active worksheet
                Worksheet ws = (Globals.Factory.GetVstoObject((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet));

                //Remove ANY existing ListObjects before we clear down or they are left partially removed
                foreach (Excel.ListObject list in ws.ListObjects)
                {
                    ws.Controls.Remove(list.Name);
                    //list.Delete(); -- this is busted
                }
                Application.DoEvents();

                //Clear down anything else in the worksheet
                ws.Cells.ClearContents();
                ws.Cells.ClearComments();
                ws.Cells.ClearFormats();

    Tuesday, January 24, 2012 5:20 PM
  • Okay, just for completeness this is the full solution for getting back to an empty worksheet after adding dynamic Excel Tables (ListObjects):

                //Attach to the active worksheet
                Worksheet ws = (Globals.Factory.GetVstoObject((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet));

                //Remove ANY existing ListObjects before we clear down or they are left partially removed
                foreach (Excel.ListObject list in ws.ListObjects)
                {
                    ws.Controls.Remove(list.Name);
                    //list.Delete(); -- this is busted
                }
                Application.DoEvents();

                //Clear down anything else in the worksheet
                ws.Cells.ClearContents();
                ws.Cells.ClearComments();
                ws.Cells.ClearFormats();

    Glad to hear that you had got it to work.

    But after glancing your code snippet, I guess that deleting the worksheet and adding a new one would be more efficient...

    Good day.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, January 25, 2012 9:30 AM
    Moderator