Salakhetdinov Shamil
mcp2004 at mail.ru
Fri Feb 27 12:56:19 CST 2009
Hi Mike, When you Automate MS Excel from within MS Access VBA, especially if there are many Automation calls then on every such call under "the DCOM hood" happens a lot of COM marshaling/demarchaling "dirty" work, which is quite time consuming therefore the way John does his Automation is the most efficient in my opinion although it would be not that easy to control what happening on MS Excel VBA macro side, how to properly handle runtime errors on that side if any etc... -- Shamil -----Original Message----- From: Mark A Matte <markamatte at hotmail.com> To: <accessd at databaseadvisors.com> Date: Fri, 27 Feb 2009 17:58:44 +0000 Subject: Re: [AccessD] Automate Excel macro with parameters > > John, > > > > I had an MDB that pulled some data from different sources...exported to several different tabs in an Excel document...and then formatted each sheet. > > > > Just curious...is there a benefit to having excel get the data and format vs. Access getting the data, exporting, and formatting the sheets? > > > > Thanks, > > > > Mark > > > > > > > Date: Fri, 27 Feb 2009 11:08:43 -0500 > > From: jwcolby at colbyconsulting.com > > To: accessd at databaseadvisors.com > > 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 > > _________________________________________________________________ > Windows Live Hotmail more than just e-mail. > http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_howitworks_022009 > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >