How can I deploy the .dna file from ExcelDna?

Question

I am trying to create an Excel (2007) Add-in that will respond to PivotTable changes, using this code:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
    Excel.Worksheet sh = this.Application.ActiveSheet;

    sh.PivotTableUpdate += new
Excel.DocEvents_PivotTableUpdateEventHandler(sh_PivotTableUpdate);
}

void sh_PivotTableUpdate(Excel.PivotTable TargetPivotTable)
{
    MessageBox.Show("sh_PivotTableUpdate event fired");
}
private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
{
}

Once the .dll was created, deploying it/generating an .xll file became a challenge; I used this post for guidance there, and do now have an .xll file.

I was able to add this to the Excel spreadsheet (.xlsx file) that I want to respond to the code (via File > Excel Options > Add-Ins > Go... > Browse), but get this err msg on adding the .xll file:

I did see that there is a *.dna file here \packages\ExcelDna.AddIn.0.33.9\content\ExcelDna-Template.dna

...but making a copy of that file and changing the name of it to [projectName].dna (Excel2010AddInForRptRunner-AddIn.dna), and then copying it to the same location as the .xll file with the .xlsx file is not the solution (no pun intended). Changing the PivotTable manually does not fire the event/I see no "sh_PivotTableUpdate event fired" message.

The .dna file does reference the .dll like so:

<ExternalLibrary Path="Excel2010AddInForRptRunner.dll" LoadFromBytes="true" Pack="true" />

So what do I need to do to resolve the err msg I get and get the .xll file to be accepted by the spreadsheet so that its code will run and the PivotTableUpdate event handler is fired?

Answer 1

There are two issues that I see in your snippet:

The first is that you seem to have mixed the VSTO Office stuff with Excel-DNA (I see this from the ThisAddIn_... code, which relates to VSTO). These don't mix at all - you can't use Excel-DNA inside a VSTO add-in project. Any namespaces that start with Microsoft.Office.Tools... indicate there is a problem. This might happen is you start with an "Office Add-In" or "Excel Add-in" as your project type.

There is a also mess in your project related to the NuGet packages - somehow the output files are inside the package directory, or the package directory has been overwritten or changed somehow. You should not have to ever edit anything under packages\...

If everything is correct, then the files you are interested in will be found in bin\Debug and bin\Release under your project directory. Usually you can redistribute only the single ...-AddIn-packed.xll file (which you can also rename if you want to).

I suggest you make a new "Class Library" project (not an Office add-in or anything like that) and install the ExcelDna.AddIn package again. Then follow the instructions in the ReadMe file that pops up to make a simple add-in with a single UDF function, and check that this runs and can be debugged and deployed correctly.

After you have that working perfectly, you can incorporate access the COM object model into your add-in by following these two steps:

• Add a reference to the Excel Interop assemblies (Microsoft.Office.Interop.Excel and Office), either directly via "Add Reference", or by installing the ExcelDna.Interop assembly from NuGet.

• Get hold of the right Application root object by calling ExcelDnaUtil.Application. The object returned can be cast to a Microsoft.Office.Interop.Excel.Application and used to get to the whole COM object model from there, and hook up your event handlers etc.