code to Save an Access Report as an Excel document

[dmposey61]

I am trying to find the code that will save an Access Report automatically as an excel document. I am using Access 2000 with Windows XP.

I am trying to add a command button on my form that will export an already created query or report as an excel document. I found the CreateNewDocument Method but that will open up a new document to create. I want to basically do a File - Save As method.

I am a beginner with VB so please be specific and descriptive!

Thank you,
Denise

 

[vbajock]

You can export the underlying query of the report to a spreadsheet using a TransferSpreadSheet macro. Open the macro editor, scroll to Transferspreadsheet. Fill in the dialog box that appears at the bottom. Hit F1 for more info and examples.

 

[bandclar]

Yes, vbajock is correct in suggesting you use the underlying query of the report. I have used the code below behind a command button to open a query as an Excel doc.

The CurrentProject.Path simply opens the excel doc in the same folder as your database.

Dim eVersion As String
eVersion = CurrentProject.Path & "\" & "OpenOfficesExcelVersion.xls"

DoCmd.OutputTo acOutputQuery, "qryOpenOfficesExcel", acFormatXLS, eVersion, True

 

[dmposey61]

Thanks vbajock & bandclar!

I had a little trouble with the TransferSpreadSheet macro when it came to Table Name. I've never used this before, so I'm sure it is due to my lack of knowledge. I ended up just exporting report to excel and user can save report on their machine if needed.

Dim stDocName As String

stDocName = "rptName"
DoCmd.OutputTo acReport, stDocName, acFormatXLS, "ReportName.xls", True


This actually works better for what I need to do. Thank you both again!
dmposey61

 

[vbajock]

Put either the tablename or a query name in the mis-named table name dialog