How can I get back a previously created Excel ListObject?

Question

I am creating a ListObject in Excel using VSTO as follows:

ListObject lo = ws_vsto.Controls.AddListObject(range, "MyList");

(The range variable is a previously defined range.)

If I then loop through the worksheet Controls collection I can find that ListObject.

However, if I save the workbook and reopen it, the Controls collection is empty. How can I get this ListObject back after re-opening so that I can continue working with it?

EDIT

I've got a bit further:

        var wb = Globals.ThisAddIn.Application.ActiveWorkbook;
        var wb_vsto = wb.GetVstoObject();


        foreach (Excel.Worksheet ws in wb.Worksheets)
        {
            var wsv = ws.GetVstoObject();
            foreach (Excel.ListObject l in ws.ListObjects)
            {
                MessageBox.Show(l.Name);
                var lo = wsv.Controls.AddListObject(l);
                Excel.Range range = lo.Range;
                range.Activate();
           }
        }

When I get to the var lo = line, I have a ListObject added to the Controls collection and available for use. However, it's DataSource property holds null. Is there an easy way to get the original data source back?

I then thought about rebuilding the data source from the information in the range. The range.Activate() line selects the list in Excel (so I know it has the right thing). However, I can't for the life of me work out how to get the data out of that range and get the address of the range. The MSDN documentation talks about the Address property but this doesn't appear to actually exist. (MSDN documentation for VSTO seems ropey at best).

Answer 1

Have you tried? Microsoft.Office.Tools.Excel.ListObject lo= Microsoft.Office.Tools.Excel.ListObject.GetVstoObject(l) (it's C# I'm not sure in VB)

From MSDN GetVstoObject, be sure to read the remarks.

Answer 2

About your first question, you created the listOject with the code

ListObject lo = ws_vsto.Controls.AddListObject(range, "MyList");

To recover the object after save/reope the workbook, try this line of code:

ListObject lo = Globals.Factory.GetVstoObject(Worksheet.ListObjects["MyList"]);