jwcolby
jwcolby at colbyconsulting.com
Fri Feb 27 18:13:21 CST 2009
I haven't yet, unfortunately work gets in the way. But I will.
Thanks for the example.
John W. Colby
www.ColbyConsulting.com
Mark Simms wrote:
> 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
>>
>
>
>