Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access exporting a Report to Excel

Posted on 2011-10-27
10
Medium Priority
?
2,781 Views
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.
1
Comment
Question by:CountryGirlMD
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37038252
is your office 2007 updated to the latest service pack?  Sp2 ?
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 600 total points
ID: 37038264
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
 

Author Comment

by:CountryGirlMD
ID: 37038331
Yes - I do have SP2 installed
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37038391
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
 
LVL 93

Expert Comment

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

Accepted Solution

by:
peter57r earned 900 total points
ID: 37038479
"Output to" uses Excel5 format which is only 16k records.

Use Docmd.Transferspreadsheet instead.
0
 

Author Comment

by:CountryGirlMD
ID: 37039249
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
 

Author Comment

by:CountryGirlMD
ID: 37039880
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
 
LVL 77

Expert Comment

by:peter57r
ID: 37039943
That's interesting. Just a bit of a pain to have to mess around.
0
 

Author Closing Comment

by:CountryGirlMD
ID: 37041061
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

810 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