[AccessD] Automate Excel macro with parameters

If the Excel macro is always the same one, why not pull the code into
Access and wrap it into a function?
Call the function each time you want to change the parameters.

Jim


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, February 27, 2009 8:51 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Automate Excel macro with parameters

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
################################################################################
If you have received this message in error, please contact the sender
immediately and be aware that the use, copying, or dissemination of 
this information is prohibited. This email transmission contains 
information from NCI Information Systems, Inc. that may be considered 
privileged or confidential and is intended solely for the named 
recipient.
################################################################################