jwcolby
jwcolby at colbyconsulting.com
Fri Feb 27 12:22:57 CST 2009
Mark, > Just curious...is there a benefit to having excel get the data and format vs. Access getting the data, exporting, and formatting the sheets? Well... That is a quasi-philosophical argument with touches of realism. In general (given I am not an Excel kinda guy) I have found it easiest to use the macro recorder to record a macro doing whatever I wanted done, apply formatting, bold, back color, whatever. Cleanup the code (the macro code is ugly) and save that as a macro. Now call the macro from Access and you are done. Create a template spreadsheet with all of the code. I wrote code in the spreadsheet to open an ado connection to SQL Server given a server name and database name. Then I open a recordset right in Excel. Iterating the recordset I pull the data and stuff it into cells. Not spectacularly fast, and perhaps even entirely inefficient, however it works. When I do not know what I am doing, I am all about getting it working, then make it better. Now that I can open the recordset and populate a single spreadsheet, I call that same code over and over, changing the name of the sheet and the view back in SQL Server. When I am done I have a spreadsheet with about 17 sheets filled out, and formatted the way I like. Doing it inside of Excel allows me to instantly look back at the sheet and SEE what I just did or didn't do. Just click on the tabs. I don't even need Access open to get the spreadsheet working. If you do it in Access, you are writing / correcting your code in Access, and playing with Excel to see it. This spreadsheet tells the client the details about the order I just processed. I used to actually go run the views in SQL Server, then cut and paste the result sets into the pages of the workbook. I got the spreadsheet automatically importing the data into the sheets and have been using it for a couple of months. I just had to copy / rename / open the template, then manually execute the code that fills the pages. MUCH better than having to manually execute views back in SQL Server and cut/paste the results into the sheets. I finally got the time to automate copy / renaming the template, and then opening the spreadsheet from a form I already use as part of my order process for the client. One more piece automated. Filling an order used to take me most of a day. It now takes me about two to four hours, depending on complexity. Someday it will get down to as little as an hour. It will probably never fall below that because the rest is configuring views out in SQL Server. John W. Colby www.ColbyConsulting.com Mark A Matte wrote: > 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