[AccessD] Automate Excel macro with parameters

Salakhetdinov Shamil mcp2004 at mail.ru
Fri Feb 27 12:50:33 CST 2009


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
> 




More information about the AccessD mailing list