Access exporting a Report to Excel

Posted on 2011-10-27
1 Endorsement
Last Modified: 2012-05-12
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.
Question by:CountryGirlMD
    LVL 119

    Expert Comment

    by:Rey Obrero
    is your office 2007 updated to the latest service pack?  Sp2 ?
    LVL 119

    Assisted Solution

    by:Rey Obrero
    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

    Author Comment

    Yes - I do have SP2 installed
    LVL 119

    Expert Comment

    by:Rey Obrero
    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.

    LVL 92

    Expert Comment

    by:Patrick Matthews
    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?
    LVL 77

    Accepted Solution

    "Output to" uses Excel5 format which is only 16k records.

    Use Docmd.Transferspreadsheet instead.

    Author Comment

    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

    Author Comment

    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
    LVL 77

    Expert Comment

    That's interesting. Just a bit of a pain to have to mess around.

    Author Closing Comment

    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............

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now