none
Excel Table create and remove dynamically RRS feed

  • Question

  •    

    Hi I am working with Excel Add-Ins.

    I want to create excel table dynamically depends on the type (type I, type II ).

    I have two type, and will know run time which type of data suppose to show in the excel sheet.

    I was creating the table by using the following code    

    if(Type==TypeI)
    {
     if(typeII table exist)
    {
                delete
    }
    worksheet.ListObjects.AddEx(XlListObjectSourceType.xlSrcRange, Range, Type.Missing, Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes, Type.Missing).Name = "Type_I_Table";

    }
    else
    {
      if(type1 table exist)
      {
    delete
      }
    worksheet.ListObjects.AddEx(XlListObjectSourceType.xlSrcRange, Range, Type.Missing, Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes, Type.Missing).Name = "Type_II_Table"; 

    }

    This work fine but some time i need to remove the created table and create another type table here i get problem how i can do that?

    so i need help for how can i show the required table using same range 



    barnes.dennis007

    Tuesday, March 5, 2013 4:26 PM

All replies

  • Hi barnes.dennis007,

    As always there are several ways to do this. To give an example this is how you could do it:

    private void AddTable(string name)
    {
        Excel.Worksheet wks = this.ActiveSheet as Excel.Worksheet;
        wks.ListObjects.AddEx(XlListObjectSourceType.xlSrcRange, wks.Range["A2:B3"], Type.Missing, Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes).Name = name; 
    }
    
    private void RemoveTable(string name)
    {
        Excel.Worksheet wks = this.ActiveSheet as Excel.Worksheet;
        foreach (Excel.ListObject table in wks.ListObjects)
        {
            if (table.Name.Equals(name))
            {
                table.Delete();
            }
        }
    }
    

    What you see here is that in the first function I create the table in Range A2 to B3 and give it the name that was passed as the parameter. In the second function I go over the the ListObjects and whenever it has the name that I passed I delete it again.

    Hope it helps,

    -= Maarten =-


    Software Engineer * MVP-Visual Developer-VSTO

    Wednesday, March 6, 2013 1:05 AM
    Moderator
  • Thanks For Quick Response, 

    I tried this but problem when use following code to get the table neam 2nd time.

                              
      foreach (ListObject listObject in sheet.ListObjects)
                        {

                            if (listObject .Name.Equals("typeI"))
                            {
                                    excelListObjectTable1 = Globals.Factory.GetVstoObject(listObject )

                            }
                        }

    Getting excetion

    The control cannot be added because a control with the name typeI already exists in the Controls collection.



    scenario is as follows.

    1st time 

      type==TypeI

        LoadTypeI table

     now change type to TypeII

     type==TypeII

      LoadTypeIITable



    works fine til here

    now 2 nd time i will set the Type==TypeI

    here i will get the exception.

    The control cannot be added because a control with the name typeI already exists in the Controls collection.



    This mean when i delete the table it get deleted from excel but not from Controls Collection so i think i need to refresh the control collection when i delete. so how i can do that.


    barnes.dennis007



    Wednesday, March 6, 2013 3:42 PM