Access exporting a Report to Excel
Posted on 2011-10-27
Access 2007 / Excel 2007
I’m trying to do some exports reports from Access to Excel using the DoCmd.OutputTo option, this is my code:
strFileName = strPath & "Report" & iCount & ".xls"
strReportName = "rDrivers" & iCount
DoCmd.OutputTo acOutputReport, strReportName, acFormatXLS, strFileName, 0
It works fine for 13 of 15 reports but on the other two I get this error: “There are too many rows to output, based on the limitation specified by the output format or by Microsoft Access”
The reports that work all have under 3000 rows – the other 2 reports have 16,800 & 19,500 rows – significantly more but still well under Excel 2007’s 1,048576 row limit or even Excel 2003’s 65,536 row limit.
And on both reports if I pull the report up on the screen and then do the export to Excel from the ribbon option it works fine.
The report has 4 sub-reports and when exported with the OutputTo command it automatically does the grouping in Excel so that users can view the summary and drill down to the detail when required.
The docmd.transferspreadsheet is not an option because of the sub reports and grouping.
If I try the command using the Excel 2007 file format - DoCmd.OutputTo acOutputReport, strReportName, acFormatXLSX, strFileName, 0 – I get this error – Run time 2282 “The format in which you are attempting to output the current object is not available”
So the question is does anybody know a way around this error or another way to export the data and retain the subreport/group features? It doesn’t make sense that I can do it from the ribbon and not from code.