[AccessD] Automate Excel macro with parameters

jwcolby jwcolby at colbyconsulting.com
Fri Feb 27 10:08:43 CST 2009


Well, that works but of course that is another manual labor task.  Even more importantly the 
spreadsheet is a template.  I just found a need to modify the template and add yet another piece. 
If the code lives in the template then I modify the template, not my access database.

The whole point of an Excel Wrapper class is:

1) The syntax is arcane if you do not use Excel
2) It allows the Access developer (me) to do things with an Excel workbook / sheet by just calling 
methods of a class, passing in parameters.
3) It allows me to set up Excel operations and click a button in access to do those things.

I have an Excel spreadsheet with 16 tabs which I populate with numbers from 16 views out in SQL 
Server.  I can then attach that spreadsheet to an email and send it off to a client.  I created a 
macro in the spreadsheet to fill in the pages, but the macro needs the name of the server and the 
name of the database in that server.

Every time I do this I have to open the spreadsheet, click tools / Macro / vbEditor, find the macro 
that I want, call it from the debug window passing the name of the server and database etc.  It is 
already way better than the cut and paste each sheet from the view over in SQL, which is what I used 
to do, but it would be even better if I could simply press an "excel" button in my Access form and 
the spreadsheet opens and the macro runs.

In fact I have done what I need by pulling the code out of my excel class wrapper and calling the 
workbook object inside of my access function, but that is ugly, and I have to do the same thing any 
time (in any code) that I run into a macro with parameters.

I have run into other places where I want to pass a paramarray off to another function that expects 
a paramarray.  Unfortunately what happens is that the paramarray gets passed into the destination as 
an array, not a lit of items in that array, and the receiving function does not know what to do with 
the array.  Sigh.

John W. Colby
www.ColbyConsulting.com


Hewson, Jim wrote:
> 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?
> 



More information about the AccessD mailing list