473,320 Members | 1,802 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Export Access Report to Excel Keeping Formatting

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
10 24331
nico5038
3,080 Expert 2GB
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
RobinAG
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
3,080 Expert 2GB
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
RobinAG
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
3,080 Expert 2GB
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
RobinAG
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
3,080 Expert 2GB
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
RobinAG
40
I used Dir(), works good. Thanks a lot!
Oct 11 '07 #9
nico5038
3,080 Expert 2GB
Glad I could help, success with your application !

Nic;o)
Oct 11 '07 #10
Thank you for all replys I have been off ill : (

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

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

Similar topics

2
by: Yisroel Markov | last post by:
Greetings, two of my users have the same front end db (A2K). Each of them has created, in addition to the standard reports, a personal customized report. Now they both want to be able to run...
1
by: Vincel2k2 | last post by:
Hi, I need to provide Access reports from our web site. When the app gets to the output command I get this error I get this error: The formats that enable you to output data as a Microsoft Excel,...
1
by: ellenh | last post by:
I have read postings on the similar subject including the posting from 2003 shown below. This process works fine to display a single page snapshot report in PowerPoint. I need to display...
1
by: Bob Alston | last post by:
Anyt one know a way to correctly export an Access 2003 report to Excel? I can use Tools - Office Links ..... just fine with a query or table. But when I try a report it just doesn't work. right...
2
by: nofear | last post by:
I used to export my reports as snapshot but now I have to export them to Excel When I export my report to a Excel Spreadsheet the report header and footer are not included Only the data gets...
1
by: anon3803 | last post by:
I have a report in Access that has a lot of grouping (sometimes over 5 group headers). When I export it to Excel where more people can use it and are familiar it creates a lot of blank rows when...
3
by: jmarcrum | last post by:
I want to export a report (that contains two separate queries, 1. Current year data, and 2. split-year data) from access into excel, but everytime I run my code and export the data to excel, it looks...
3
by: hawaiijeff | last post by:
I have a report that I built in Access 2002. I built it with the report wizard, but then went into design mode and added a extra information which was calculations off the existing fields in the...
2
by: KrazyKasper | last post by:
I have an MS-Access (2003) report that I export to Excel; clean up some formatting; and use the data in a MS-Word mailmerge for letters to customers. In addition to the usual customer name,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.