Can I use Microsoft.Office.Tools.Excel in Excel DNA project, or is there another way to accomplish databinding a Table ?

Jul 7, 2011 at 3:46 PM

I have code that worked in the VSTO version of an Excel Addin

    Microsoft.Office.Tools.Excel.ListObject lo = Globals.ThisAddIn.VSTOWorksheet.Controls.AddListObject(r, "lo1");
    lo.AutoSetDataBoundColumnHeaders = true;
    lo.DataSource = dt; //some DataTable

I was using this API because its declarative databinding syntax. And the Excel.Interop API didn't have methods such as AutoSetDataBoundColumnHeaders..

If I can get Microsoft.Office.Tools.Excel.ListObject imported, how will I resolve the Controls collection on which I call AddListObject without the VSTO stuff inside Excel DNA ?

Any solution would be nice, even if it involves scrapping my code, but in general I'd like to understand when to use which API inside of Excel DNA in order to accomplish this databinding stuff.

Stackoverflow version of this question: http://stackoverflow.com/questions/6603876/can-i-use-microsoft-office-tools-excel-in-excel-dna-project-or-is-there-another

Coordinator
Jul 7, 2011 at 10:01 PM

Hi Dean,

VSTO adds some extensions on top of the Excel object model. I have no experience with VSTO, and for stuff like the ListObjects I can't easily tell where Excel's object model ends and the extended VSTO wrapper objects start.

The boundary is basically this: Microsoft.Office.Interop.Excel can be used from Excel-DNA (so this is the ListObject interface you can use: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.listobject_members.aspx). Microsoft.Office.Tools.Excel is part of VSTO.

Also, I really have no idea whether the VSTO libraries can be used together with Excel-DNA. The issue would be getting the VSTO libraries initialized and hooked up - it might be tricky.

It might be worth it for you to try to re-implement those object model extensions yourself, on top of the Excel object model. I don't think VSTO is doing anything you couldn't do yourself.

-Govert

Jul 15, 2011 at 7:57 PM

I tried referencing VSTO dlls, and their extension methods (Microsoft.Office.Tools.Excel.Extensions), but .GetVstoObject() and similar other methods for getting the extended VSTO object return null. It seems that there is a Factory that must be initialized, which is only properly initialized in a VSTO solution.

So it looks like I'm back to using iteration code to extract DataTables into string[,] arrays and then setting .Value on a range. Not as clean as lo.DataSource = theDataTable, but it will do..

Does Excel DNA have methods for simplifying working with tables of data ? Adding rows at the bottom (and extending the named range over it), etc.. are the features I'd like. I don't really need 'binding' in the strict sense, I just feel like a good solution would be iteration-free (or at least hide the iteration quite well)

Dean

Coordinator
Jul 17, 2011 at 7:57 AM

Hi Dean,

From Excel-DNA you can access the complete Excel COM object model. So you could implement exactly the same wrappers that VSTO does, in your own add-in.

But I have don't have any experience working with data tables myself (exposed through the ListObject COM classes). I suggest you record some of the action you are interested in into a VBA macro. That might show you the right way to deal with the ListObjects, and you can then translate this some wrapper classes in your add-in.

-Govert