Here's the technique I use for this task using Excel automation from
Access. First, I allow the user to select an Excel template from a
list of templates in a specific folder. The automation code then
launches Excel if it is not already running and instructs Excel to
create a new workbook from the template. All templates have a report
sheet as well as a raw data sheet. The raw data sheet has a macro
that will do much of the work. The Access query or underlying
recordset for a report is then written to the raw data sheet with
field names on the first row. The automation code then runs the raw
data sheets macro.
On the Excel side, the macro can do anything but it commonly creates
named ranges for the data on the raw data sheet so the report sheet
can use formulas to look up and display data. The macro can also use
the named ranges to copy data to the report sheet. Such a macro could
also automatically print the report as well.
If you need more ideas, our product demo on our web site has some
examples and you can download some Excel examples to see the VBA code.
HTH
Rick Collard
www.msc-lims.com
On 6 Nov 2003 07:32:26 -0800,
o6t6i@unb.ca (Steven Stewart) wrote:
[color=blue]
>I have a user who has been using Excel for a while to keep statistics
>and print reports. She finds using it cumbersome because of long
>formulas and a lot of copying and pasting.
>
>I have designed a database for her which is intended to make things a
>lot easier; however, I don't have a lot of experience with Access and
>I find that designing the reports in Access is tedious. I want to be
>able to print the reports (which are simply based on queries) from
>Excel because I find Excel much easier for the formatting.
>
>I also need this task to happen automatically. For instance, using a
>user-interface, she simply clicks on the type of report she wants and
>Excel opens up displaying the report exactly as it should be printed
>(all the correct formatting, etc.). She then merely has to click
>Print in Excel. Of course, it would be good if Excel opened, printed,
>and then closed automatically, but I am considering the possiblity
>that she may want to modify the report or the page settings on
>occaison, although I doubt this.
>
>Is it possible to export the data from an Access query into, for
>instance, a pre-formatted Excel template? This would help me greatly.
>
>Any assistance would be much appreciated.
>
>--
>Steven Stewart[/color]
