How can I add a button to a Microsoft Access report to export it to Excel / PDF?
+1
A:
Modules: Sample Excel Automation - cell by cell which is slow
Modules: Transferring Records to Excel with Automation
Note though the Excel automation suggested is actually against a query as exporting reports to Excel makes them exceedingly ugly. If I recall correctly this feature was removed in Access 2007.
A2000ReportToPDF is an Access 2000 database containing a function to convert Reports and Snapshot files to PDF documents. No PDF Printer driver is required. Free.
Tony Toews
2009-11-17 00:33:06
Tony, according to Allen Browne in http://allenbrowne.com/Access2007.html the export to Excel problem was fixed in A2007 SP2.
David-W-Fenton
2009-11-18 00:09:11
A:
AFAIK you can't "add" it to the report. But on the form that opens the report, you could add a button with the following code:
DoCmd.OutputTo acOutputReport, "ReportName", acFormatXLS,
"c:\YourFolderName\ReportName - " & Format(Date, "yyyymmdd") & ".xls"
Only Access 2007 has support for PDF's. So you will either need to install a PDF printer or use Tony Toews suggestion
Christian Payne
2009-11-17 00:38:47
Christian, have you ever tried to export a report to Excel? It's real ugly.Actually A2007 does have builtin PDF support so I should've mentioned that.
Tony Toews
2009-11-17 02:43:10
PDF export is really easy to add in earlier versions of Access, either with Stephen Lebans' snapshot converter, or by using something like PDFCreator, which is automatable from VBA.
David-W-Fenton
2009-11-18 00:11:59
+1
A:
Christian has suggested a command button on a form, but you could also create a toolbar for the report with a button on it that would export the report to Excel. But as Tony says, the results are going to be ugly.
I would say that more useful would be a button that exports the data displayed in the report to an Excel spreadsheet. Formatting wouldn't be as pretty, but it would be much more useful and manipulable. For that, you'd use DoCmd.TransferSpreadsheet and a saved Query as your export source (equivalent to the Recordsource of the report).
David-W-Fenton
2009-11-17 03:31:43
It is possible to get pretty good formatting in Excel with HTML, and it is not so difficult to use a template in VBA and fill in the gaps.
Remou
2009-11-17 11:30:53
Does Excel accept HTML without doing something special? Word doesn't handle it well via automation (or, at least, I've not found out how) -- that gets me to my main reason for commenting, and that's http://www.codinghorror.com/blog/archives/001311.html , where we're lectured on parsing HTML. Is there an Access-usable solution for parsing HTML? I need it bad!
David-W-Fenton
2009-11-18 00:10:57