How to resolve: There are too many rows to output, based on the limitation specified by the output format or by Microsoft Office Access ?

I create an Access Report with the following code in my Access application to send the report output to an Excel file:
The reason I use the report is to create sub-totals.
There are 18,500 rows to output and I guess the limit is 16,536.
I get the message:

There are too many rows to output, based on the limitation specified by the output format or by Microsoft Office Access.

Do you know how I can resolve the error ?
-------------------------------------------------
Set com = New ADODB.Command
    With com
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procDetailRangeRpt3YRPlus"
        Set .ActiveConnection = cn
       .Execute
    End With
     
    intReport = intYearSP + 1
    ExportedFile = "C:\UDL\DTLRANGE" & "_" & intReport & "_" & Format(Now, "mmddhhnnss") & ".XLS"
   
    DoCmd.OutputTo acOutputReport, "RptDetailReport", acFormatXLS, ExportedFile
   
    Beep
    MsgBox "Detail Range Report has been exported to Excel", vbOKOnly, ""
    If isFileExist(ExportedFile) Then StartDocDetail ExportedFile

---------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE procDetailRangeRpt3YRPlus
AS

If  Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = ' tblDtlRange' AND TYPE = 'U')
DROP TABLE tblDtlRange

SELECT C.OfficeNumber, C.CustomerNumber, C.ResStateCode, C.ResCountryCode, C.CitizenCode, C.DateLost,
C.DateOfBirth, C.SSN, C.TaxId, C.FixedFieldInd, C.FirstName, C.MiddleInitial, C.LastName, C.StreetAddr1,
C.StreetAddr2, C.City, C.State, C.Zip, C.Country, C.RedFlag,       
tblMthRanges.MthFrom, tblMthRanges.MthTitle,
--CASE WHEN P.MarketValue IS NULL THEN P.CashBalance ELSE P.MarketValue END AS [Acct Value],  
Sum(isnull(MarketValue,0)+isnull(CashBalance,0)) AS AcctValue,
CONVERT(char(10), GETDATE() - Day(GETDATE()) + 1, 121) AS DFrom,    
CONVERT(char(10), DATEADD(Month, -[MthTo] + 1, GETDATE() - Day(GETDATE())  + 1), 121) AS DateFromC,
tblMthRanges.MthTo,
CONVERT(char(10),DATEADD(MONTH, -MthFrom, CONVERT(varchar(8), GETDATE(), 102)+ '01'), 121) AS DateToC
INTO tblDtlRange
FROM tblMthRanges, tblCustomers As C INNER JOIN tblProducts As P ON C.CustomerNumber=P.CustomerNumber AND C.OfficeNumber=P.OfficeNumber
WHERE (((DATEDIFF(M, [DateLost], DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) >= [MthFrom])) AND
((DateDiff(M,[DateLost],DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) < [MthTo])))
AND [MthFrom] = 36 AND [MthTo] = 1000
GROUP BY
tblMthRanges.MthFrom, tblMthRanges.MthTitle, C.OfficeNumber, C.CustomerNumber,
DATEADD(M, [MthTo] * -1, DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())), tblMthRanges.MthTo,
DATEADD(M,[MthFrom]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE())),C.ResStateCode, C.ResCountryCode,
C.CitizenCode, C.DateLost, C.DateOfBirth, C.SSN, C.TaxId, C.FixedFieldInd, C.FirstName, C.MiddleInitial, C.LastName,
C.StreetAddr1, C.StreetAddr2, C.City, C.State, C.Zip, C.Country, C.RedFlag

If  Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = ' tblRCustR' AND TYPE = 'U')
DROP TABLE tblRCustR

SELECT tblDtlRange.OfficeNumber, tblDtlRange.CustomerNumber, tblDtlRange.AcctValue, tblDollarRanges.DollarTitle, tblDollarRanges.DollarFrom
INTO tblRCustR
FROM tblDtlRange
INNER JOIN tblDollarRanges
     ON tblDtlRange.AcctValue BETWEEN tblDollarRanges.DollarFrom AND tblDollarRanges.DollarTo
WHERE tblDtlRange.AcctValue > 9.00
ORDER BY AcctValue DESC

SELECT tblRCustR.OfficeNumber, tblRCustR.CustomerNumber, tblRCustR.AcctValue, tblRCustR.DollarTitle

FROM tblRCustR
ORDER BY
case when DollarTitle   = 'WITH LESS THAN $1K ASSETS' then 6
         when DollarTitle   = 'WITH $1K TO LESS THAN $10 K ASSETS' then 5
         when DollarTitle   = 'WITH $10K TO LESS THAN $25 K ASSETS' then 4
         when DollarTitle   = 'WITH $25K TO LESS THAN $50 K ASSETS' then 3
         when DollarTitle   = 'WITH $50K TO LESS THAN $100 K ASSETS' then 2
         when DollarTitle   = 'WITH $100K + ASSETS' then 1
end
 , OfficeNumber, CustomerNumber
GO
zimmer9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zimmer9Author Commented:
I just wanted to mention that I am using Access 2003.
0
zimmer9Author Commented:
The record source of my report is not a query (it's a table).
0
Eric ShermanAccountant/DeveloperCommented:
May I ask ....

If you have 18,500 rows already in a table why do you need to export them to Excel???  

A database will handle what a spreadsheet is limited by plus additional records.  Anything you can do in Excel you can accomplish in Access.

ET
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

zimmer9Author Commented:
Of course you may ask me anything. I personally work with databases but my users are accountants and they look at data in Excel despite it's limitations.
0
Patrick MatthewsCommented:
Hi zimmer9,

I guess this explains why you only get 16,000 rows:
http://support.microsoft.com/default.aspx?scid=kb;en-us;269539

XL 5 had a row limit of 16,384.

With that many records, and with subtotals being interspersed with the real data, why not just leave it in the report?

Regards,

Patrick
0
Eric ShermanAccountant/DeveloperCommented:
I understand and I have been at those crossroads before "being an Accountant".  

1.) Either break your output up into multiple worksheets/workbooks.

2.) Build a nice little user interface form for the database that holds the records so the accountants can interact with the data.

Personally, I would teach them how to use some of the basic Access functions (mainly queries and reports).

ET
0
ajkampCommented:
Excel is limited to ~65k rows, so you shouldn't be having any issues there (18k isn't terrible).

When you output the report, Access defaults to Excel 5 when using OutputTo. If you output your reports via Macro, you can specify Excel 97-2003.

I have a feeling the issue is probably in the file format that is being exported. You can always try to write a macro to output one of your larger reports into a test excel file to see if that is indeed the issue.
0
ajkampCommented:
Had I refreshed before posting, I would've seen that Patrick looked up the XL 5 limits.

Calling a macro to export your reports will work.
0
zimmer9Author Commented:
ExportedFile = "C:\UDL\DTLRANGE" & "_" & intReport & "_" & Format(Now, "mmddhhnnss") & ".XLS"
DoCmd.OutputTo acOutputReport, "RptDetailReport", acFormatXLS, ExportedFile

Do you know how I would implement a macro to export my report to Excel. I have a report named "RptDetailReport" and the Excel file being written to is a combination of "C:\UDL\DTLRANGE" & "_" & intReport & "_" & Format(Now, "mmddhhnnss") & ".XLS"



 
0
zimmer9Author Commented:
Is it DoCmd.OpenReport    (RptDetailReport  ,acPreview)   ?
0
ajkampCommented:
In the Macro editor (not the VBA editor) select "OutputTo" and then enter the parameters

Object Type --> Report
Object Name --> RptDetailReport
Output Format --> Microsoft Excel 97-2003
Output File --> \\UNC Path\Filename.xls

In your VBA, you would change the OutputReport to:
DoCmd.RunMacro "your new macro name"

You can then rename your file output to include the "C:\UDL\DTLRANGE" & "_" & intReport & "_" & Format(Now, "mmddhhnnss") & ".XLS"

0
zimmer9Author Commented:
I have inserted my new macro name as follows:    DoCmd.RunMacro "SummDetailReport"

How would I go about renaming the Output File from the Macro parameter value     --> Output File --> \\UNC Path\Filename.xls
to my file name "C:\UDL\DTLRANGE" & "_" & intReport & "_" & Format(Now, "mmddhhnnss") & ".XLS"


0
ajkampCommented:
DoCmd.Rename "C:\UDL\DTLRANGE" & "_" & intReport & "_" & Format(Now, "mmddhhnnss") & ".XLS",,"\\UNC Path\Filename.xls"

---------

The \\UNC Path\Filename was assuming you were going to save the file to a common share on your network.

It could be as simple as C:\rptDetailReport
or as complex as
\\WestCoastRegion\Seattle21\Datacorp\Shared\Common\Strategic Development\DTLRange_rptDetailReport.xls
0
zimmer9Author Commented:
To recap:

I now have a macro named "SummDetailReport"

it is comprised of the following:

Action
OutputTo

Action Arguments
Object Type      Report
Object Name     rptDetailReport
Object Format   Microsoft Excel 97 - 2003
Output File        \\UNC Path\Filename.xls
--------------------------------------------------
In my Access application I now have the following code:

To recap:

I now have a macro named "SummDetailReport"

it is comprised of the following:

Action
OutputTo

Action Arguments
Object Type      Report
Object Name     rptDetailReport
Object Format   Microsoft Excel 97 - 2003
Output File        \\UNC Path\Filename.xls
--------------------------------------------------
In my Access application I now have the following code:


To recap:

I now have a macro named "SummDetailReport"

it is comprised of the following:

Action
OutputTo

Action Arguments
Object Type      Report
Object Name     rptDetailReport
Object Format   Microsoft Excel 97 - 2003
Output File        \\UNC Path\Filename.xls
--------------------------------------------------
In my Access application I now have the following code:

DoCmd.Rename "C:\UDL\DTLRANGE" & "_" & intReport & "_" & Format(Now, "mmddhhnnss") & ".XLS",,"\\UNC Path\Filename.xls"
DoCmd.RunMacro "SummDetailReport"
------------------------------------------------
When I run my application, the following line is highlighted:

DoCmd.Rename "C:\UDL\DTLRANGE" & "_" & intReport & "_" & Format(Now, "mmddhhnnss") & ".XLS",,"\\UNC Path\Filename.xls"

I get the error message:

Run time error '2487'
the Object type argument for the action or method is blank or invalid

Do you know how I can resolve this error ?


0
thenelsonCommented:
Use the TransferSpreadsheet Method instead:
DoCmd.TransferSpreadsheet acexport, acSpreadsheetTypeExcel9, tablename, filename[, hasfieldnames][, range]

tablename can be a query
filename is the spreadsheet path/name
0
zimmer9Author Commented:
I have a table with the following values in table tblA :

OfficeNumber           CustomerNumber      AcctValue           DollarTitle
761                         3463634                   170,000.00         WITH 100K AND OVER
754                         3434643                   150,000.00         WITH 100K AND OVER
654                         3266564                     80,000.00         WITH 50K TO LESS THAN 100K
243                         5644533                     75,000.00         WITH 50K TO LESS THAN 100K
743                         8954986                     45,000.00         WITH 25K TO LESS THAN 50K
253                         4643345                     32,000.00         WITH 25K TO LESS THAN 50K
343                         6543354                     28,000.00         WITH 25K TO LESS THAN 50K
-------------------------------------------------------------------------------------------------------------------------------------------------
My objective is to create a spreadsheet that is formatted as follows: The table doesn't have the data in the format I need to display
on a report. That is why I created a report instead which gives me sub-totals and a grand total.

Do you know how I can create a query to generate the following values comprised of detail values followed by sub-total values:
I guess it would be a UNION query.

OfficeNumber           CustomerNumber      AcctValue           DollarTitle
761                         3463634                   170,000.00         WITH 100K AND OVER
754                         3434643                   150,000.00         WITH 100K AND OVERTOTAL                    
SUBTOTAL               2                             320,000.00         WITH 100K AND OVER
654                         3266564                     80,000.00         WITH 50K TO LESS THAN 100K
243                         5644533                     75,000.00         WITH 50K TO LESS THAN 100K
SUBTOTAL               2                             155,000.00         WITH 50K TO LESS THAN 100K
743                         8954986                     45,000.00         WITH 25K TO LESS THAN 50K
253                         4643345                     32,000.00         WITH 25K TO LESS THAN 50K
343                         6543354                     28,000.00         WITH 25K TO LESS THAN 50K
SUBTOTAL               3                              105,000.00        WITH 25K TO LESS THAN 50K
GRANDTOTAL           7                             580,000.00    
0
Eric ShermanAccountant/DeveloperCommented:
Why not just output the detail records to an Excel Spreadsheet then use the Sub-Total function in Excel to sub-total on Dollar Title.  This is really pretty easy to do.  Let me know if you need further instructions.

Or

Create the first query grouped on the Dollar Title field, Summ the AcctValue field and Count the CustomerNumber field.  Then make a second query that will Union the first query to the table.  I think someone else posted this suggestion to your other question on the same topic.

ET

0
zimmer9Author Commented:
I'm working on it now. What you and Nestorio stated is right on the money. Thanks. Sorry for being redundancy. I'm trying to make a good faith effort to do it on my own based on your insights.
0
GusKCommented:
The easiest method to output this volume of rows..... if you don't mind doing it is......

a) Run the Query
b) Select All records in the result set
c) Copy
d) Goto Excel
e) Paste

I just tested this at my end and within 30 secs could paste in 48,000 rows.

A little more manual, but very effective.   As for subtotalling use a pivot table as suggested in my other response to your question.  Sit it over the data output.   This will allow subtotalling based on various dimensions a required by your users.   Using Data Subtotals can be limited, pivots are very flexible and give you multi dimensional analysis capabilities.

Hope this helps
GusK
0
thenelsonCommented:
Have you considered "runcommand acCmdOutputToExcel"  Outputs selected object in RTF format?
Here is a link on it's usage:
http://home.clara.net/tkwickenden/
0
thenelsonCommented:
The "runcommand acCmdOutputToExcel" would be two lines

  DoCmd.SelectObject acReport, strName, True            'False if the report is open
  DoCmd.RunCommand acCmdOutputToExcel

It was created for A2k version so it may not have the 18k limit.  I used it for 14K records.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ajkampCommented:
Does the file you exported via macro have all of the data in it?
0
zimmer9Author Commented:
Nelson, you are the only one to come up with a solution that could migrate a report with over 16,384 rows to Excel and this
turned out to be just what I needed with just 2 lines of code.

thanks for all your assistance.

regards,
zimmer
0
thenelsonCommented:
Well that's absolutely fantastic that it worked and just two lines of code.  I remembered this question when I ran across the acCmdOutputToExcel on another project.  Glad I could help.  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.