Access exporting a Report to Excel

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.
CountryGirlMDAsked:
Who is Participating?
 
peter57rCommented:
"Output to" uses Excel5 format which is only 16k records.

Use Docmd.Transferspreadsheet instead.
0
 
Rey Obrero (Capricorn1)Commented:
is your office 2007 updated to the latest service pack?  Sp2 ?
0
 
Rey Obrero (Capricorn1)Commented:
see this link for reference

Unless you install the Office 2007 suite Service Pack 2, you cannot export a report to an Excel format in Access 2007

http://support.microsoft.com/kb/934833
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
CountryGirlMDAuthor Commented:
Yes - I do have SP2 installed
0
 
Rey Obrero (Capricorn1)Commented:
from the link i posted above  


Symptom 3
Consider the following scenario. You run a Microsoft Visual Basic for Applications (VBA) procedure that contains the SendObject method. In this method, the value of the ObjectType argument is acSendReport, and the value of the OutputFormat argument is acFormatXLS. Alternatively, you run a VBA procedure that contains the OutputTo method. In this method, the value of the ObjectType argument is acOutputReport, and the value of the OutputFormat argument is acFormatXLS. In this scenario, you may receive the following error message:
Run-time error '2282': The format in which you are attempting to output the current object is not available.



Workaround 3
To work around symptom 3 in "Symptoms" section, use one of the following methods.
Method 1

   1. In the VBA procedure, change the value of the ObjectType argument to one of the following:
          * acSendTable
          * acSendQuery
          * acOutputTable
          * acOutputQuery
   2. Change the value of the ObjectName argument to the record source of the report.

Method 2

   1. In the VBA procedure, change the value of the OutputTo argument to another value. For example, change the value of the OutputTo argument to one of the following values:
          * acFormatPDF
          * acFormatRTF
          * acFormatSNP
          * acFormatTXT
          * acFormatXPS
   2. Change the value of the OutputFile argument to the correct file name extension.

0
 
Patrick MatthewsCommented:
Did you update strFileName to use an xlsx extension and not an xls extension?

Also, does the computer where you are running this have just Excel 2007 installed, or Excel 2007 and Excel 2003?
0
 
CountryGirlMDAuthor Commented:
to matthewspatrick --
  yes  -  I used the filename with the.xlsx extention with the acFormatXLSX
         and .xls with acFormatXLS

I only have the Office / Excel 2007 suite - non of the 2003 versions are installed

to Peter57r
when you do the export to excel from the ribbon - you have a choice of excel 5 format or Excel 97-2003 - when you choose the 97-2003 format it works - I've been able to export 19,515 rows

So, I checked some of the files generated by the VBA code and you are right they are being saved in Excel 5 format

and the transfersreadsheet option won't work because I need the grouping of the sub-report data in excel
0
 
CountryGirlMDAuthor Commented:
To Peter57r

I've found a solution -- the differences between the command line version of OutputTo and the Ribbon export got me to thinking - there is a third option -- the MACRO version of OutputTo

The macro version does give you the option to select the 97-2003 file format and I was able to export my 19,000 row report successfully

So instead of looping through the reports with a counter I'll have to have a seperate line in the macro for each one -- tedious -- but it will work  -- thanks for the help
0
 
peter57rCommented:
That's interesting. Just a bit of a pain to have to mess around.
0
 
CountryGirlMDAuthor Commented:
Thanks for pointing me in the right direction

I'd still like to know why the export to an XLSX with acFormatXLSX option doen't work............
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.