sparta93 sparta93 - 1 month ago 19
C# Question

How to delete table in Excel but preserve comments using VSTO?

I'm using the snippet below to convert an Excel range with data into a table. In some cases, I need to delete the table, but preserve comments in the cells. Is there a way I can achieve that? Also, is there a way to toggle the headers on/off? I tried the different options under

XlListObjectHasHeaders: Microsoft.Office.Interop.Excel.XlYesNoGuess.
but those didn't work. thanks for your help.

finalRange.Worksheet.ListObjects.AddEx(
SourceType: Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange,
Source: finalRange,
XlListObjectHasHeaders: Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes);


I delete the table structure like this -

finalRange.Worksheet.ListObjects.Item[1].Delete();


EDIT (solution for multiple tables):

foreach (var table in sheet.ListObjects)
{
Microsoft.Office.Interop.Excel.ListObject tempObj = (Microsoft.Office.Interop.Excel.ListObject)table;
Microsoft.Office.Interop.Excel.Range tempRange = tempObj.Range;
tempRange.ClearContents();
}

Answer

These both presuppose your Table variable is lo:

Excel.ListObject lo = ws.ListObjects["Table1"];

To hide the header row in an Excel table:

lo.ShowHeaders = false;

To Remove the table but retain the comments, use the range.Clear method instead of the table.Delete.

Excel.Range tableRange = lo.Range;
tableRange.ClearContents();
Comments