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.
10 24331
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)
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: - 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.
The DoCmd.TransferSpreadsheet command requires a "saved" query to run.
You can however fill a query dynamically with the SQL text like: -
Dim qd as DAO.Querydef ' Requires that the Microsoft Excel library is linked under Tools/Refrences
-
-
set qd = currentdb.querydefs("qryDummy") ' this asks for a saved query we can use
-
-
qd.SQL = "<place her your SELECT statement>"
-
-
' Here the DoCmd.TransferSpreadsheet can be executed for qryDummy
-
DoCmd.TransferSpreadsheet .....
-
Getting the idea ?
Nic;o)
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.
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)
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.
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)
I used Dir(), works good. Thanks a lot!
Glad I could help, success with your application !
Nic;o)
Thank you for all replys I have been off ill : (
I will go through all and take a look, cheers
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |