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

Exporting Access Data to create an Excel File

100+
P: 418
Hi,
I am using Access 2007. I have been using an
Access report to create an Excel file by manually entering the same data.

I was wondering if it's possible to export data from an Access report to fill in a customized Excel template. Right now when I select export to Excel from Access report - the result is a messy excel file that requires a lot of time to clean it.

Any lead on this issue will be greatly appreciated.

Thanks.
Jun 23 '14 #1
Share this Question
Share on Google+
3 Replies


zmbd
Expert Mod 5K+
P: 5,397
Welcome to bytes,
There are a few ways to do this...

For me, quite often I will use the DoCmd.TransferSpreadsheet Method (do a search here for several different threads covering this method) which will send a query to the worksheet. I then use this worksheet to feed the "pretty" report worksheets and graphs.

You can also use automation to create a copy of your template file, then populate via named-ranges. There's an example of automation in our insights articles: Application Automation it starts with outlook but also covers a little bit about excel.

Finally, you can link into the access database and then refer to the information directly... the drawback here is that the links can be broken.
Jun 23 '14 #2

NeoPa
Expert Mod 15k+
P: 31,768
Hi Mareena.

Depending on your exact requirements there are various possible approaches to take. Z has covered a few of them.

I like to use Application Automation from Access where the data is specified in a DAO.Recordset. Once the spreadsheet file is open I use the .CopyFromRecordset() method of the Excel.Range class to do the basic data transfer. After that I do any tidying up required.
Jun 23 '14 #3

strive4peace
P: 36
Hi Mareena,

as Adrian said, CopyFromRecordset is good to use. To create a file based on a template instead of just a blank workbook:

Expand|Select|Wrap|Line Numbers
  1. xlApp.Workbooks.Add _
  2.    Template:= _
  3.    CurrentProject.Path _
  4.    & "\Templates\Filename.xlt"
WHERE
xlApp is your Excel application object variable. Here is a download with code to write to Excel. It is commented so you can learn:

Document Calculated Fields in Queries
http://www.rogersaccesslibrary.com/f..._topic619.html

... plus you get to learn about a cool SQL statement :)


Warm Regards,
Crystal

*
(: have an awesome day :)
*
Jun 24 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.