Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

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
Avatar of zimmer9
zimmer9
Flag of United States of America image

ASKER

I just wanted to mention that I am using Access 2003.
Avatar of zimmer9

ASKER

The record source of my report is not a query (it's a table).
Avatar of Eric Sherman
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
Avatar of zimmer9

ASKER

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.
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
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
Avatar of ajkamp
ajkamp

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.
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.
Avatar of zimmer9

ASKER

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"



 
Avatar of zimmer9

ASKER

Is it DoCmd.OpenReport    (RptDetailReport  ,acPreview)   ?
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"

Avatar of zimmer9

ASKER

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"


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
Avatar of zimmer9

ASKER

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 ?


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

tablename can be a query
filename is the spreadsheet path/name
Avatar of zimmer9

ASKER

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

Avatar of zimmer9

ASKER

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.
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
Have you considered "runcommand acCmdOutputToExcel"  Outputs selected object in RTF format?
Here is a link on it's usage:
http://home.clara.net/tkwickenden/
ASKER CERTIFIED SOLUTION
Avatar of thenelson
thenelson

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Does the file you exported via macro have all of the data in it?
Avatar of zimmer9

ASKER

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