Prev: crosstab parameterized query
Next: Cumulative Percent
From: accesshacker on 10 May 2010 16:07
I am trying to output a query as an excel file and save it to a specific
location. Here is my code.

DoCmd.OutputTo acOutputQuery, "Qry:SKU_ALL_2", acFormatXLS, _
"C:\RH DOC\LCM_PROJECT\LCM_QRY" & "\" & "LCM_QRY" & "_" & "PERIOD" & "_" &
([Forms]![LCM_DATA]![PER]) & ".xls", False

I get a run time error '2306' that states there too many rows to output,
based on the limitation specified by the output format or by Microsoft
Access. There are 36,464 records. When I take acFormatXLS and the file path
and have Access prompt me for the output, I am able to create the file in the
folder specified.

Not sure what I am doing wrong. Any help will be greatly appreciated!
From: Jerry Whittle on 10 May 2010 17:33
acFormatXLS saves the Excel file in an older version, Excel 95 I believe,
that can only handle about 32,000 rows.

Look into using TransferSpreadsheet instead. Something like below can handle
about 64,000 rows.

DoCmd.TransferSpreadsheet acExport, 8, "Cargo_tariff",
"c:\temp\Cargo_tariff.xlsx", False, ""

If you have Access 2007 changing the 8 to 10 allows about 1 million rows.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"accesshacker" wrote:

> I am trying to output a query as an excel file and save it to a specific
> location. Here is my code.
>
> DoCmd.OutputTo acOutputQuery, "Qry:SKU_ALL_2", acFormatXLS, _
> "C:\RH DOC\LCM_PROJECT\LCM_QRY" & "\" & "LCM_QRY" & "_" & "PERIOD" & "_" &
> ([Forms]![LCM_DATA]![PER]) & ".xls", False
>
> I get a run time error '2306' that states there too many rows to output,
> based on the limitation specified by the output format or by Microsoft
> Access. There are 36,464 records. When I take acFormatXLS and the file path
> and have Access prompt me for the output, I am able to create the file in the
> folder specified.
>
> Not sure what I am doing wrong. Any help will be greatly appreciated!
From: accesshacker on 10 May 2010 18:08
Hi Jerry,

Thanks for the response, that worked. Also, I did come across another method
that works as well, just had to change a little of the code. It is as follows.

DoCmd.OutputTo acOutputQuery, "Qry:SKU_ALL_2", acSpreadsheetTypeExcel9, _

Changed the acFormatXLS to acSpreadsheetTypeExcel9

Thanks again for the quick response!

"Jerry Whittle" wrote:

> acFormatXLS saves the Excel file in an older version, Excel 95 I believe,
> that can only handle about 32,000 rows.
>
> Look into using TransferSpreadsheet instead. Something like below can handle
> about 64,000 rows.
>
> DoCmd.TransferSpreadsheet acExport, 8, "Cargo_tariff",
> "c:\temp\Cargo_tariff.xlsx", False, ""
>
> If you have Access 2007 changing the 8 to 10 allows about 1 million rows.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "accesshacker" wrote:
>
> > I am trying to output a query as an excel file and save it to a specific
> > location. Here is my code.
> >
> > DoCmd.OutputTo acOutputQuery, "Qry:SKU_ALL_2", acFormatXLS, _
> > "C:\RH DOC\LCM_PROJECT\LCM_QRY" & "\" & "LCM_QRY" & "_" & "PERIOD" & "_" &
> > ([Forms]![LCM_DATA]![PER]) & ".xls", False
> >
> > I get a run time error '2306' that states there too many rows to output,
> > based on the limitation specified by the output format or by Microsoft
> > Access. There are 36,464 records. When I take acFormatXLS and the file path
> > and have Access prompt me for the output, I am able to create the file in the
> > folder specified.
> >
> > Not sure what I am doing wrong. Any help will be greatly appreciated!
 | 
Pages: 1
Prev: crosstab parameterized query
Next: Cumulative Percent