How to make an Excel Command copy a DataTable to Excel worksheet

[Homero]

Hi,

is there any tutorial on how to use the ExcelCommand attribute on C#?

How do I access it on Excel?

I'm trying to copy a DataTable that I declare in a C# program to a
table in a worksheet in Excel.

Can somebody help me? At least on how to use ExcelCommand. I was able
to use ExcelFunction and access it on Excel as a function, but I
couldn't do the same with ExcelCommand.

Appretiate your help!

Homero

[Govert van Drimmelen]

Hi Homero,

The ExcelCommand allows your add-in to define macros that run from a
menu or ribbon button.
The (public static) method method must have no parameters and return
type 'void'.

You can also type the macro name into the 'Run Macro' dialog box - it
won't be listed there but the Rub button will become enabled if you
type a valid macro name, or you can execute it from VBA using
Application.Run.

The easiest way to get a menu for your macro is to set some values in
the [ExcelCommand] attribute. Under Excel 2007 this gives you a menu
under the 'Add-Ins' tab. You can also add a custom ribbon to your
Excel-Dna Add-In, or build a whole CommandBar hierarchy, but that's a
bit more advanced.

So your method might start with:
[ExcelCommand(MenuName="Range Tools", MenuText="Square Selection")]
public static void SquareRange()
{...}

There is a nice example of how to dump data into Excel using the Excel-
Dna and the C API style (XlCall.xxxx) in my answer here:
http://stackoverflow.com/questions/3840270/fastest-way-to-interface-between-live-unsaved-excel-data-and-c-objects
In this example I read a range, process and write to a new sheet; it
might give you a good start.

Another approach to implement your method is to use the COM automation
interface to talk to Excel. For this you'll need to get hold of the
right Application object - call
ExcelDna.Integration.ExcelDnaUtil.Application to get the right object.
From there you are talking to the same COM object model as you would
from VBA. To interact with the COM object model you can either
reference the PIA in your assembly, use late-binding through
reflection using Invoke, or use the 'dynamic' type in C# 4. Each
method has its pros and cons.

Hope this gets you started.

Cheers,
Govert

[Homero]

Thanks! That was very helpful.

But now I'm trying to make a ExcelCommand that has parameters, like I
would if it was in VBA. How can I do this?

Homero

On 21 jan, 09:42, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi Homero,
>
> The ExcelCommand allows your add-in to define macros that run from a
> menu or ribbon button.
> The (public static) method method must have no parameters and return
> type 'void'.
>
> You can also type the macro name into the 'Run Macro' dialog box - it
> won't be listed there but the Rub button will become enabled if you
> type a valid macro name, or you can execute it from VBA using
> Application.Run.
>
> The easiest way to get a menu for your macro is to set some values in
> the [ExcelCommand] attribute. Under Excel 2007 this gives you a menu
> under the 'Add-Ins' tab. You can also add a custom ribbon to your
> Excel-Dna Add-In, or build a whole CommandBar hierarchy, but that's a
> bit more advanced.
>
> So your method might start with:
> [ExcelCommand(MenuName="Range Tools", MenuText="Square Selection")]
> public static void SquareRange()
> {...}
>
> There is a nice example of how to dump data into Excel using the Excel-

> Dna and the C API style (XlCall.xxxx) in my answer here:http://stackoverflow.com/questions/3840270/fastest-way-to-interface-b...

> In this example I read a range, process and write to a new sheet; it
> might give you a good start.
>
> Another approach to implement your method is to use the COM automation
> interface to talk to Excel. For this you'll need to get hold of the
> right Application object - call
> ExcelDna.Integration.ExcelDnaUtil.Application to get the right object.
> From there you are talking to the same COM object model as you would
> from VBA. To interact with the COM object model you can either
> reference the PIA in your assembly, use late-binding through
> reflection using Invoke, or use the 'dynamic' type in C# 4. Each
> method has its pros and cons.
>
> Hope this gets you started.
>
> Cheers,
> Govert
>
> On Jan 20, 11:01 pm, Homero <homero...@gmail.com> wrote:
>
>
>
> > Hi,
>
> > is there any tutorial on how to use the ExcelCommand attribute on C#?
>
> > How do I access it on Excel?
>
> > I'm trying to copy a DataTable that I declare in a C# program to a
> > table in a worksheet in Excel.
>
> > Can somebody help me? At least on how to use ExcelCommand. I was able
> > to use ExcelFunction and access it on Excel as a function, but I
> > couldn't do the same with ExcelCommand.
>
> > Appretiate your help!
>

> > Homero- Ocultar texto das mensagens anteriores -
>
> - Mostrar texto das mensagens anteriores -

[Govert van Drimmelen]

Hi,

Excel-DNA does not currently support macros that take parameters - no
technical reason, just an oversight of mine years ago.
It's on the CodePlex Issue Tracker, so you can vote for the feature
there...

You would only be able to call these from VBA using Application.Run -
menu buttons couldn't pass parameters.

My best suggestion is to move the VBA code that would be calling it to
Excel-DNA too. From within the Excel-DNA add-in you can just call the
method directly.

-Govert

[Homero]

Wait a minute, with "You would only be able to call these from VBA
using Application.Run " you mean I can call my C# written ExcelCommand
with Application.Run? How do I do it? I tried many ways. I don't care
if I can't call it from a menu button. I just want to use it in vba
macros.

Gratefully

Homero

On 26 jan, 10:54, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi,
>

> Excel-DNA does not currently support macros that takeparameters- no

> technical reason, just an oversight of mine years ago.
> It's on the CodePlex Issue Tracker, so you can vote for the feature
> there...
>
> You would only be able to call these from VBA using Application.Run -
> menu buttons couldn't passparameters.
>
> My best suggestion is to move the VBA code that would be calling it to
> Excel-DNA too. From within the Excel-DNA add-in you can just call the
> method directly.
>
> -Govert
>
> On Jan 26, 2:40 pm, Homero <homero...@gmail.com> wrote:
>
>
>
> > Thanks! That was very helpful.
>

> > But now I'm trying to make a ExcelCommand that hasparameters, like I

> > would if it was in VBA. How can I do this?
>
> > Homero
>
> > On 21 jan, 09:42, Govert van Drimmelen <gov...@icon.co.za> wrote:
>
> > > Hi Homero,
>
> > > The ExcelCommand allows your add-in to define macros that run from a
> > > menu or ribbon button.

> > > The (public static) method method must have noparametersand return
> > > type 'void'.
>
> > > You can also type themacroname into the 'RunMacro' dialog box - it

> > > won't be listed there but the Rub button will become enabled if you

> > > type a validmacroname, or you can execute it from VBA using
> > > Application.Run.
>
> > > The easiest way to get a menu for yourmacrois to set some values in

> > > - Mostrar texto das mensagens anteriores -- Ocultar texto das mensagens anteriores -

[Govert van Drimmelen]

Application.Run("MyCommand")

myResult = Application.Run("MyFunction", "Input1", "Input2")

[Homero]

I mean with parameters. If using Application.Run I can call macros
with parameters

[Govert van Drimmelen]

Not at the moment.

How about this:
You make a hidden function which takes the parameters you want to
pass.
Then call it from VBA followed by a call to the macro to run it.

public static class HackForCommandWithParameters
{
private static string _param1;
private static double _param2;

[ExcelFunction(IsHidden=true)]
public static object SetParameters(string param1, int param2)
{
_param1 = param1;
_param2 = param2;
return true;
}

public static void DoWork()
{
DoTheRealWork(_param1, _param2);
}
}

And then you call it as:

Application.Run("SetParameters", "Hello", 42)
Application.Run("DoWork")

-Govert