[AccessD] Automate Excel macro with parameters

Nope, didn't work with the spreadsheet.  As near as I can tell what happens is that the param array 
gets turned into a single string, with two items separated by commas.  Since the macro expects two 
parameters, the join() provides a single string as the first parameter.  The call to run the macro 
returns an error "parameter not optional" when it finds nothing in the second parameter.

This does immediately provide a workaround however for this instance.  In my macro I can EXPECT an 
array()!  I was of course using the normal

MyMacro(strServer as string, strDB as string)

which is of course what you would use to manually call this thing passing in the parameters from the 
immediate window.

However if I just rewrite the macro definition to:

MyMacro(varParams() as variant)

I can then parse out the varParams() array and recover the server and db name.

Of course that does not solve the big picture.

I regret to inform you that your Access God title awarded for this solution has been rescinded.

John W. Colby
www.ColbyConsulting.com


Stuart McLachlan wrote:
> How about 
> 
> xlBook.Application.Run "MacroName", Join(MyParamArray()",")
>