Mark A Matte
markamatte at hotmail.com
Fri Feb 27 11:58:44 CST 2009
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