Export Data from Report or Query to Excel


Nurse Nancy

I have read 100's of posts on this and I am still unsure how to proceed, so
here goes yet another post regarding exporting data to Excel

I created both a query and a report for my users but they are unhappy with
both when i export to Excel.

straight to excel won't work because I have lots of comments and data needed
as headers, and some information that would be the same for every record in
the query needs to be part of the header,,,,

I have access 2007 and the report I generated only looks good when it is
exported as PDF. IN RTF and html i loose most of the headers..

When creating the report in access I am able to create the header from
information on the underlying query

But the file needs to get sent to a bunch of different people and they need
to fill in information from the detail lines of the report and send it back,
so a report won't work. I guess it needs to be excel or some type of html
form that users can fill out.

If I create an excel template and then use VBA to fill the template,,, which
I'll need help doing..... my issue is there are certain places in the
headings that need data from the underlying query

For example the heading will say
Advertiser: (and here I need to populate from a field in my query)
Start Campaign Date: ( from query) End Campaign Date ( From Query)

Standard Comments: (about 10 lines that needs to in the heading )

So, do i output to a report in Access and then export that to an excel
preformatted Template, or output to query?

Or do i create the template using VBA,,,god help me, if i have to do this!!!

If the answer is create an excel template and fill it,,,
Should my template already have Column headings in it starting on say,,, row
20 ?
Should my template have the first 19 rows filled in with the standard
verbage and leave room for the what will get populated from access?


Roger Carlson

One relatively simple way to do this is to export the query using the
TransferSpreadsheet method, but have the data populate a pre-formatted
spreadsheet. The trick here is to export the data to a NEW tab in the Excel
workbook and link the fields from the new tab into the formatted

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ExportToExcel.mdb " which illustrates how to do this. You
can download it for free here:

--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question