Get excel selection content when clicking a ribbon button using exceldna

Question

I'm trying to read and update the user's Selection (the excel's range currently selected). This must happens when the user click on the custom ribbon's button. Ribbon works well (basic functions like messagebox triggers fine) but following code fails.

[ComVisible(true)]
public class RibbonHandler : ExcelRibbon
{

    public void OnPressMe(IRibbonControl control1)
    {

        ExcelReference ActiveRange = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);

        object[,] dummyData = (object[,])XlCall.Excel(XlCall.xlfValue, ActiveRange);

        int rows = dummyData.GetLength(0);
        int cols = dummyData.GetLength(1);
        for (int i = 0; i < rows; i++)
        {
            for (int j = 0; j < cols; j++)
            {
                dummyData[i, j] = "foo";
            }
        }

        ExcelAsyncUtil.QueueAsMacro(() => { ActiveRange.SetValue(dummyData); });

    }
}

It triggers an XlCallException at the first line, whatever the size of the selection (one cell , ten cells, one or multiple rows, ...)

Many web examples have a range object as function's input, it may be an hint, but I don't understand how clicking a button will sends ActiveSheet.Selection object as parameter in a function.

What am I missing to get the user's selection using a Ribbon's button ?

Answer 1

XlCall.Excel is a call to the Excel C API which cannot be done from within the handler of a Ribbon action.

You should use the COM object model to interact with the active sheet.

First, install the NuGet package ExcelDna.Interop so you can get intellisense when accessing the COM object model, then access the current Excel instance through ExcelDnaUtil.Application

using Excel = Microsoft.Office.Interop.Excel;
// ...

[ComVisible(true)]
public class RibbonHandler : ExcelRibbon
{
    public void OnPressMe(IRibbonControl control1)
    {
        var excel = (Excel.Application)ExcelDnaUtil.Application;
        var selection = (Excel.Range)excel.Selection;

        // ...
    }
 }