By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,069 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Export Data to Excel for Report Printing

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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


Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.