I'm developing an Excel 2007 add-in using Visual Studio Tools for Office (2008). I have one sheet with several ListObjects on it, which are being bound to datatables on startup. When they are bound, they autosize correctly.
The problem comes when they are re-bound. I have a custom button on the ribbon bar which goes back out to the database and retrieves different information based on some criteria that the user inputs. This new data comes back and is re-bound to the ListObjects - however, this time they are not resized and I get an exception:
ListObject cannot be bound because it cannot be resized to fit the data. The ListObject failed to add new rows. This can be caused because of inability to move objects below of the list object.
Inner exception: "Insert method of Range class failed"
I was not able to find anything very meaningful on this error on Google or MSDN. I have been trying to figure this out for a while, but to no avail.
Basic code structure:
//at startup DataTable tbl = //get from database listObj1.SetDataBinding(tbl); DataTable tbl2 = //get from database listObj2.SetDataBinding(tbl2); //in buttonClick event handler DataTable tbl = //get different info from database //have tried with and without unbinding old source listObj1.SetDataBinding(tbl); <-- exception here DataTable tbl2 = //get different info from database listObj2.SetDataBinding(tbl2);
Note that this exception occurs even when the ListObject is shrinking, and not only when it grows.