Use a button to export a report to excel

[aaronmib]

Can you export a report into excel using a button and VBA? If so what is the code to do that?

 

[Alansidman]

There are built in features in Access to allow this. No code required unless absolutely necessary.

Look here:

http://www.howtogeek.com/howto/microsoft-office/export-an-access-2003-report-into-excel-spreadsheet/

Alan

 

[aaronmib]

My end-users are running Access Runtime and they don't have that option so if there is a way to code it that would be awesome.

 

[Alansidman]

Here is some code for a command button on click event to export a report to Excel.

Private Sub Command9_Click()
Dim reportname As String
Dim theFilePath As String
reportname = "MaxPrice"
theFilePath = "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\"
theFilePath = theFilePath & reportname & "_" & Format(Date, "yyyy-mm-dd") & ".xls"
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportname, theFilePath, True
MsgBox "Look on your desktop for the report."
End Sub

This will export the report to excel and place it on the users desktop with as an Excel 2002 file with the current date amended to the file name.

If you want it as a later file type, change the file type in the Docmd line and change theFilePath also. Also change the report name to reflect yours.

When I tested, I put the command button in the Header.

Alan

 

[AccessBlaster]

Also have a look at Ken Snell's site for more export options.