How can I add a button to an Access report to export it to Excel / PDF?

Question

How can I add a button to a Microsoft Access report to export it to Excel / PDF?

Answer 1

I just combined some of the previous answers and this is my final code block that exports a report to excel and then opens said excel file.

Private Sub Command79_Click()
'initialize variables
Dim strReportName As String
Dim strPathUser As String
Dim strFilePath As String

'set variables
strReportName = "AlarmLetterForSF"
strPathUser = Environ$("USERPROFILE") & "\my documents\"
strFilePath = strPathUser & strReportName & Format(Date, "yyyymmdd") & ".xls"

'export to excel
DoCmd.OutputTo acOutputReport, strReportName, acFormatXLS, strFilePath

'launch excel file
Dim Shex As Object
Set Shex = CreateObject("Shell.Application")
Shex.Open (strFilePath)
End Sub

Answer 2

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.