Search code examples
c#excelexcel-dna

ExcelDNA - Creating a table: xlcAddListItem or another way

I'm trying to figure out how to use ExcelDNA to create a table from an ExcelReference range.

With Excel you would usually do something like:

range2.Worksheet.ListObjects.Add(Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange, range2, System.Type.Missing, Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes, System.Type.Missing).Name = "uniquesomething";

I've been trying to do something similar with ExcelDNA using xlcAddListItem, but it's pretty rough without any proper documentation. Has anyone had any success? Basically take the ExcelDNA array resizer example and then take the grid output and turn it to a table.

So far I tried

ExcelDna.Integration.SourceItem mySourceItemHopefully = (ExcelDna.Integration.SourceItem)XlCall.Excel(XlCall.xlcAddListItem, 1, myExcelReference);
mySourceItemHopefully.Name = "uniquesomething";

But I get an access violation error.

Solution

  • For this you will have to use the COM object model, which you can safely use from inside an Excel-DNA add-in as long as you:

    1. use ExcelDnaUtil.Application to get hold of the root Application object, and

    2. make all COM object model calls from the main thread in a macro or COM callback context.

    You might need to get a COM Range object from the ExcelReference.

    This can be as simple as:

    static Range ReferenceToRange(ExcelReference xlref)
{
    string refText = (string)XlCall.Excel(XlCall.xlfReftext, xlref, true);
    dynamic app = ExcelDnaUtil.Application;
    return app.Range[refText];
}

    (Here the 'dynamic' Application might also be typed as a Microsoft.Office.Interop.Excel.Application, if you have a reference to the PIA assembly.)

    For more complicated ExcelReferences with multiple rectangles, you can start with the VB.NET code from here: https://github.com/Excel-DNA/Registration/blob/882517eed2137d2b2f9b4b794803258d20e5a174/Source/ExcelDna.Registration.VisualBasic/RangeParameterConversion.vb

    Adding Tables is not supported by the C API. Microsoft have not extended the xlcXXX macros to cover newer features in recent versions of Excel (since Excel 2000). (However, the list of xlfXXX functions have been kept up-to-date until Excel 2013.)

    The two macro functions you list do not refer to the type of tables you are making - you can see this in the MacroHelp help file that you can find here: http://www.xceed.be/Blog.nsf/dx/excel-macro-function-help-file-for-windows-7

    ADD.LIST.ITEM (xlcAddListItem): "Adds an item in a list box or drop-down control on a worksheet or dialog sheet control."

    TABLE (xlcTable): "Equivalent to choosing the Table command from the Data menu. Creates a table based on the input values and formulas you define on a worksheet. Use data tables to perform a "what-if" analysis by changing certain constant values in your workbook to see how values in other cells are affected." So this is about Data Tables, which are different to the 'ListObject' tables you're trying to create.