.NET - Excel ListObject autosizing on databind

Question

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"
Reason: Microsoft.Office.Tools.Excel.FailureReason.CouldNotResizeListObject

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.

Answer 1

Just an idea of something to try to see if it gives you more info: Try resizes the list object before the exception line and see if that also throws an exception. If not, try and resize the range object to the new size of the DataTable.

You say that this happens when the ListObject shrinks and grows. Does it also happen if the ListObject remains the same size?

Answer 2

If anyone else is having this problem, I have found the cause of this exception. ListObjects will automatically re-size on binding, as long as they do not affect any other objects on the sheet. Keep in mind that ListObjects can only affect the Ranges which they wrap around.

In my case, the list object which was above the other one had fewer columns than the one below it. Let's say the top ListObject had 2 columns, and the bottom ListObject had 3 columns. When the top ListObject changed its number of rows, it had no ability to make any changes to the third column since it wasn't in it's underlying Range. This means that it couldn't shift any cells in the third column, and so the second ListObject couldn't be properly moved, resulting in my exception above.

Changing the positions of the ListObjects to place the wider one above the smaller one works fine. Following the logic above, this now means that the wider ListObject can shift all of the columns of the second ListObject, and since there is nothing below the smaller one it can also shift any cells necessary. The reason I wasn't having any trouble on the initial binding is that both ListObjects were a single cell.

Since this is not optimal in my case, I will probably use empty columns or try to play around with invisible columns if that's possible, but at least the cause is now clear.

Answer 3

I've got a similar issue with refreshign multiple listobjects. We are setting each listObject.DataSource = null, then rebinding starting at the bottom listobject and working our way up instead of the top down.