Mark Simms
marksimms at verizon.net
Fri Feb 27 12:52:03 CST 2009
Hey John - did you try Application.Evalute ?
Here's a quick and dirty example...
Private Sub testevS(ParamArray parms() As Variant)
Dim i As Long
Dim strMsg As String
For i = LBound(parms) To UBound(parms) - 1
strMsg = strMsg & parms(i) & ","
Next
strMsg = strMsg & parms(i)
MsgBox "sub parms=" & strMsg
End Sub
Sub testeval()
Application.Evaluate ("testevS(" & """x""" & ",123" & ")")
End Sub
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Friday, February 27, 2009 1:23 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Automate Excel macro with parameters
>
> 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 LiveT HotmailR.more than just e-mail.
> >
> http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_hm_just
gotbetter_howitworks_022009
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>