Export Data to Excel for Report Printing

[Steven Stewart]

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

[Rick Collard]

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, o6***@unb.ca (Steven Stewart) wrote:

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