Display windows forms using Excel DNA

Jan 10, 2013 at 10:57 AM

Hi all,

newbie question - I have used Excel DNA to create some simple UDFs, but would like to start using it for something more complex.  As a starting point, I would like to display a windows form that can be used to extract information from an oracle database. For example, the form could have a gridview showing a table in the database that the user can filter.

Is this kind of thing possible using Excel DNA? Can I add buttons to the ribbon, and post search results back to the spreadsheet? If so, do I need to set up the ribbion in the .dna file, or do I do it in the inherited class?

Any tips or samples would be much appreciated. Thanks

Nick

 

Jan 10, 2013 at 11:36 AM

A little bit of research (About 10 mins!) has shown me how easy it is to get your own Ribbon up and running. However, I am still struggling to get it to show my windows form.

I have the following test button set up...

<button id='plotButton' label='Plot' image='M' size='normal' onAction='LoadForm'/>

 

And

[ComVisible(true

)]  

 

 

 

public class TestRibbon : ExcelRibbon

{

 public void LoadForm()

{

 TestChart ts = new TestChart

();

ts.Show();

}

 

Where TestChart is a winows form. Any ideas why this doesnot work? If I use a messageBox it works fine.

Thanks

 

Jan 10, 2013 at 11:50 AM

Not sure what happened with the whitespace in that last post! Hopefully it is still readable....

Coordinator
Jan 10, 2013 at 2:25 PM

Hi Nick,

I don't think LoadForm() method is being called. To handle the button event you have to use one of the following approaches:

1. In the xml, set:  

onAction="RunTagMacro" tag="LoadForm"

and then make LoadForm a "public static" so that it is registered as a macro, or

2. In the xml, set:

onAction="LoadForm"

and make the signature of the handler:

public void LoadForm(IRibbonControl control) {...}

In the first case, the base class method ExcelRibbon.RunTagMacro(...) will be called, which will in turn call the (public static) macro LoadForm. In the second case your event handler is called directly, but the signature must match the ribbon onAction event signature for the onAction event of a button.

The difference is that the first approach allows you to call the C API (via XlCall.Excel or ExcelReference) from the handler, while the second approach passes in a reference to the control.

-Govert

Jan 10, 2013 at 2:59 PM

Thanks Govert, all working now.

Could you elaborate a bit on what the C API enables me to do, or direct me to some resources that describe this in more detail?

Regards,

Nick

Coordinator
Jan 10, 2013 at 5:47 PM

Hi Nick,

The Excel SDK is documented here: http://msdn.microsoft.com/en-us/library/office/bb687883.aspx

The actual macro functions available via the C API are documented in this help file (from 1997): http://support.microsoft.com/kb/128185

In general, the C API is less flexible than the COM interface (newer Excel features were not added to the C API), but in some cases it may be faster, e.g when reading and writing large blocks of data to the sheet.

Internally, Excel-DNA uses the C API to integrate with Excel.

-Govert