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

Export Access Report to Excel Keeping Formatting

P: 7
Is it possible to export a report from Access to Excel and keep all the formmating.

i.e. Lines, boxes etc...

Or do you need to create an excel template and then use a macro or some VB code to run an access query to generate the excel sheet with the pre-defined formatting?

Thanks in advance.
Oct 10 '07 #1
Share this Question
Share on Google+
10 Replies


nico5038
Expert 2.5K+
P: 3,072
You can't keep the formatting from a report when exporting to Excel or Word :-(

I just use the report's query and the "DoCmd.TransferSpreadsheet" command to get data exported. When formating is needed, you can do this with Macro's in Excel, or manage every thing from VBA code (called "automation")

Sorry for the bad news....

Nic;o)
Oct 10 '07 #2

P: 40
Can you use DoCmd.TransferSpreadsheet to export based off of a query in code, rather than a query built in the database? I have a select query in a string, I put it in a recordset, and I want to export that recordset as an Excel file using DoCmd.TransferSpreadsheet, and I've tried putting the recordset as the table name in the expression:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, 8, rst, "filename", True
But it gives me the error: An expression you enteres is the wrong data type for one of the arguments.
Oct 11 '07 #3

nico5038
Expert 2.5K+
P: 3,072
The DoCmd.TransferSpreadsheet command requires a "saved" query to run.

You can however fill a query dynamically with the SQL text like:
Expand|Select|Wrap|Line Numbers
  1. Dim qd as DAO.Querydef ' Requires that the Microsoft Excel library is linked under Tools/Refrences
  2.  
  3. set qd = currentdb.querydefs("qryDummy")  ' this asks for a saved query we can use
  4.  
  5. qd.SQL = "<place her your SELECT statement>"
  6.  
  7. ' Here the DoCmd.TransferSpreadsheet can be executed for qryDummy
  8. DoCmd.TransferSpreadsheet .....
  9.  
Getting the idea ?

Nic;o)
Oct 11 '07 #4

P: 40
Great, works perfect.

One related question: Is there anyway to check using VBA code, if an excel doc already exists in a given folder with a certain name? I have multiple people using this code to make exports, and I want to alert them if the file has already been made.
Oct 11 '07 #5

nico5038
Expert 2.5K+
P: 3,072
In a case like that I just add the username to the reportname and optionally the current date or (when applicable) the requested reportingdate.

Just use in the excelsheetname some stringing like:
docmd.transferspreadsheet .... , "C:\temp\myexcel_" & format(Date(),'yyyy-mm-dd') & ".xls

Getting the idea ?

Nic;o)
Oct 11 '07 #6

P: 40
That's what I normally do, but it's the same exact report, and to avoid duplication I want to stop the export and inform the person it's been done before.
Oct 11 '07 #7

nico5038
Expert 2.5K+
P: 3,072
Then check the Dir() command. It will return only a value when the specified path/file has been found.
An alternative is to use the FileObject.

Nic;o)
Oct 11 '07 #8

P: 40
I used Dir(), works good. Thanks a lot!
Oct 11 '07 #9

nico5038
Expert 2.5K+
P: 3,072
Glad I could help, success with your application !

Nic;o)
Oct 11 '07 #10

P: 7
Thank you for all replys I have been off ill : (

I will go through all and take a look, cheers
Dec 10 '07 #11

Post your reply

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