Export Microsoft Access Reports to Excel

Question

Hi all,

I am using the below routine to export Access Queries successfully now I need to export some Access Reports but same routine throwing bellow error. Anyone can help me to modify or write a new Query to export reports from Access.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Rpt\_SPECD\_BendAngles", conPath & "Metal\_SPX\_PCMCd.xls", True 

Thanks for your support.

Answer 1

You have to change the OutputFile argument as well as the ObjectName argument:

DoCmd.OutputTo ObjectType:=acOutputReport, ObjectName:="Rpt_SPECD_BendAngles", OutputFormat:=acFormatXLS, OutputFile:=conPath & "Metal_SPX_sPECD.xls"

DoCmd.OutputTo ObjectType:=acOutputReport, ObjectName:="Rpt_SPECD_BoltSelectionFilter", OutputFormat:=acFormatXLS, OutputFile:=conPath & "SomeOtherName.xls"

Answer 2

Dear HansV Thanks for the quick reply,

I can export Report with above mention routine but when I export more than one report in one go (Example find below) last report will survive and rest of reports generate early replaced. Is this possible routine can add worksheets, not generate workbook every time.

DoCmd.OutputTo ObjectType:=acOutputReport, ObjectName:="Rpt\_SPECD\_BendAngles", OutputFormat:=acFormatXLS, OutputFile:=conPath & "Metal\_SPX\_sPECD.xls" 

DoCmd.OutputTo ObjectType:=acOutputReport, ObjectName:="Rpt\_SPECD\_BoltSelectionFilter", OutputFormat:=acFormatXLS, OutputFile:=conPath & "Metal\_SPX\_sPECD.xls" 

Thanks in advance.

Answer 3

DoCmd.TransferSpreadsheet can only export tables and select queries. It cannot handle reports.

You can use DoCmd.OutputTo for this purpose:

DoCmd.OutputTo ObjectType:=acOutputReport, ObjectName:="Rpt_SPECD_BendAngles", OutputFormar:=acFormatXLS, OutputFile:=conPath & "Metal_SPX_PCMCd.xls"

But please keep in mind that this is only a bare-bones export of the data in the report, all formatting will be lost.