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?

Solution 1

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).

Solution 2

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

Solution 3

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.

Solution 4

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

View more solutions

Comments

[Tony Toews]

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.

[Fionnuala]

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.

[David-W-Fenton]

Tony, according to Allen Browne in allenbrowne.com/Access2007.html the export to Excel problem was fixed in A2007 SP2.

[David-W-Fenton]

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 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]

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.

[HackSlash]

If you add , True to the end of your OutputTo command it will open the Excel file for you. No need for ShellX. It's called the AutoStart option.