>How does one pass parameters to an Excel macro from a VFP app?

Jun,

I did some playing with Excel on this and here's what I came up with. Before I go further, let me state that I am not an expert at Excel and there may be a way to do this more directly but this is what I came up with.

The basic theory is to create an addin with the procedures/functions you want to call. You then install it in Excel and you can call the functions with the Evaluate() method.

Here's how it works.

Create a NEW worksheet. Add a module to the VBA project. Then add this procedure to the module.

Function AddCalc(Number As Single, Number2 As Single) As Double
    AddCalc = Number + Number2
End Function

Then, save the workbook as an XLA file.

Close it.

Now, go to Tools | Addins and browse for the .XLA file you created and make sure that it is added to the list and checked.

To test whether everything is OK, create a new workbook in Excel and insert the following formula in Cell A:1

=AddCalc(10,20)

You should get a value of 30.

Once you have done this, you can close excel.

Now, note that the AddCalc function requires two parameters. The only way I found that I could call the function at all within VFP was either to insert the function into a cell and read back the value or to use the Evaluate method like this (assuming oExcel is the name of the reference to Excel).

? oExcel.Evaluate("=AddCalc(10,20)")

That should display 30.

I can't believbe that there isn't an easier way to call the addin function but I have not yet figured out a way. But this one works.

By the way, if you want to install the addin yourself, you can use the AddIns() collection to do that. basically, you would:

oExcel.AddIns.Add(<Addin file name>)
oExcel.AddIns(<Addin file name>).Installed = .T.

and you should be OK.

I hope this helps.