Print Access Report to Excel

Topic

New Reply

Hi,

I have rptITDReports that I need to print to excel AND have excel automatically open with the report. I can send the query to excel but it doesn’t give the totals that are in the report. Presently they have a macro for the old version of their report that prints the report in excel format. The problem is that they have to then hunt to find the report. This was not a big deal form them but now there are new users in the office and they want to simplify it but having the report automatically open in excel vs having to print to excel and then hunt to find it……………………trying to save mouse clicks.

Thanks,
Leesha

Reply | Quote

Replies

It is possible to export an Access report (not the query) to Excel, and then use Automation to open that file so it is visible on the workstation. However there are issues with 2007 unless you have SP2 installed. And I’m not sure about totals lines at the bottom – they should work, but….. and after some testing, it appears that headers and footers do not in either 2002 or 2007. So if you totals are there, you won’t get them. In fact it appears it exports the data source for the report in most regards. Thus to really get what the users are asking for would probably involve Automating Excel and creating the report that way. Which is a fair bit of work…

Reply | Quote

>>It is possible to export an Access report (not the query) to Excel, and then use Automation to open that file so it is visible on the workstation.

This is what I’m trying to do but am not totally sure how to code it.

>>However there are issues with 2007 unless you have SP2 installed. And I’m not sure about totals lines the bottom – they should work, but…..

Figures!! And of course this is yet another account that has a mixture of Access versions. I grow to hate 2007 by the day. I know this is because I haven’t worked it in enough to “think like it”.

Reply | Quote

I don’t have Access 2007, but in earlier versions exporting a report to Excel is rather disappointing – only the detail records are exported, the group headers and footers, report header and footer and page header and footer are lost.
You *can* automatically open the exported file:

DoCmd.OutputTo ObjectType:=acOutputReport, ObjectName:=”rptITDReports”, OutputFormat:=acFormatXLS, OutputFile:=”ITDReport.xls”, AutoStart:=True

Reply | Quote

This worked perfectly. The headers and the totals are there. I’ll have to save this code to my “cheat sheet” files! Now lets hope it works in 2007 LOL. Worked great in my 2002 version.

Thanks,
Leesha

Reply | Quote

Interesting – it appears the DoCmd.OutputTo works differently than the export from the Print Preview of a report. That’s good to know. Thanks!

Reply | Quote