[AccessD] Automate Excel macro with parameters

Salakhetdinov Shamil mcp2004 at mail.ru
Fri Feb 27 11:04:33 CST 2009


Hi John,

Why not:

1. a) Write parameterrs into a simple text file with each parameters on a separate line in MS Access VBA...
   b) Read parameters from text file in MS Excel VBA(?) macro?

   If you run many instances of MS Excel then you can make different text files with parameters for different instances, and pass the name/fullpath of this file as the only parameter in your xlBook.Application.Run call

OR

2. Start MS Excel instance using Shell(...) and command line then you can pass as many custom params as you wish I suppose (needs checking)

BTW, for the case when ParamArray should be passed through as another ParamArray I always used static Select Case as in your below sample - and I had I believe up to 30 params to pass this way - never had any issues with that - that coding style is looking ugly - yes - but it works well :)

Thank you.

--
Shamil


-----Original Message-----
From: jwcolby <jwcolby at colbyconsulting.com>
To: Access Developers discussion and problem solving<accessd at databaseadvisors.com>
Date: Fri, 27 Feb 2009 11:08:43 -0500
Subject: Re: [AccessD] Automate Excel macro with parameters

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




More information about the AccessD mailing list