[AccessD] Automate Excel macro with parameters

How about 

xlBook.Application.Run "MacroName", Join(MyParamArray()",")

-- 
Stuart

On 27 Feb 2009 at 9:50, jwcolby wrote:

> I am trying to figure out how to automate an excel macro with an unknown number of parameters.  I 
> doubt it is possible but I thought I would ask.
> 
> If you want to run a macro in Excel you have to get a pointer to the workbook.  You then use the syntax:
> 
> 	xlBook.Application.Run "MacroName", "string parameter 1", intParameter2, etc
> 
> In other words, you can pass as many parameters as the macro needs.  So...
> 
> I have long ago written a class (of course) that wraps Excel functionality.  It can open, modify, 
> close etc an Excel workbook.  An excellent example BTW of where it is useful to create a class that 
> does not inherit anything.  To my knowledge even .Net cannot inherit an Excel application.
> 
> So one of the methods of this class can run a macro in the workbook.  However to this point it can 
> only automate a macro that does not take parameters.  I want to add a ParamArray to my method to 
> feed in parameters to be sent to the macro.
> 
> The only way I can see to do this (and it is UGLY) is to build a case statement that looks at how 
> many variables are in the paramarray and just select a different xlbook.Application.Run statement:
> 
> (pseudocode)
> 
> select case ubound(MyParamArray)
> case 1
> 	xlBook.Application.Run "MacroName", MyParamArray(0)
> case 2
> 	xlBook.Application.Run "MacroName", MyParamArray(0), MyParamArray(1)
> case 3
> 	xlBook.Application.Run "MacroName", MyParamArray(0), MyParamArray(1), MyParamArray(3)
> end select
> 
> Do that for a number that would meet most cases and punt.
> 
> I HATE punting!
> 
> Has anyone ever solved the problem of passing the contents of a ParamArray to another object that 
> accepts a paramarray?
> 
> -- 
> John W. Colby
> www.ColbyConsulting.com
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com