jwcolby
jwcolby at colbyconsulting.com
Fri Feb 27 13:07:08 CST 2009
I tried to do named ranges and couldn't get it figured out in time so I just went the brute force method. I will go back and revisit that. If I can get that working then I can pop it in the Excel class wrapper and it will be there the next time. Thanks! John W. Colby www.ColbyConsulting.com Salakhetdinov Shamil wrote: > John, > > You can also try to use XML serialization/deserialization using MS XML Core Services (MSXMLxx.dll it's installed with IE/MS Office 2003/2007 or you can get it installed manually). But if your static Select Case solution is already written and works OK I'd not change anything. > > As for formatting your excel output - if the output format is of fixed width and height then you can just copy and paste the whole template in XL macro VBA code... > > As for getting data from MS SQL and putting them into XL speardsheet - you can use ADO recordset to put its data into all adjacent cells almost instantly by one VBA code line using > > CopyFromRecordSet method of XL Range object > > http://msdn.microsoft.com/de-de/library/microsoft.office.interop.excel.range.copyfromrecordset.aspx > > Well, you might be doing that already. > > BTW, that could be another option to pass variable qty of params using disconnected/manually built ADO recordsets: I mean you can just create ADO recordset instance in VBA code and define as many fields as you need set their values as params - then you can pass this ADO recordset instance to MS Excel instance as parameter.... > > 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 12:35:53 -0500 > Subject: Re: [AccessD] Automate Excel macro with parameters > >> Shamil, >> >> Thanks for the response. The parameters in a text file sounds almost as ugly as case statement. >> >> My Excel class is expecting to be able to do things with an Excel application, open a worksheet, >> poke values out to cells and so forth. That syntax I quoted appears to pick up the existing and >> already open Excel application instance, it is not opening a new Excel application (AFAICT). >> >> In my own code if I need to process a param array passed in I can do that but in this case it is a >> method of a class in Excel and that method simply doesn't know what to do with an array passed in. >> The case sounds like the answer. >> >> I hate ugly almost as much as I hate punting. >> >> John W. Colby >> www.ColbyConsulting.com >> >> >> Salakhetdinov Shamil wrote: >>> 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 >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> >