[AccessD] Automate Excel macro with parameters

Write the parameters to cells on a sheet named something like 'Parameters' in a workbook based on the template.  Iterate the parameters until you encounter a blank cell.  The workbook macro can delete the sheet when it's done.

Ciao 

 

Jürgen Welz 

 

Edmonton, Alberta 

 

jwelz at hotmail.com


> >>>>> 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?
> >>>>>
> >>>> --
> >>>> AccessD mailing list
> >>>> AccessD at databaseadvisors.com
> >>>> http://databaseadvisors.com/mailman/listinfo/accessd
> >>>> Website: http://www.databaseadvisors.com


_________________________________________________________________
The new Windows Live Messenger. You don’t want to miss this.
http://www.microsoft.com/windows/windowslive/products/messenger.aspx