Export to Excel limited to 65000 rows

[neelsfer]

I use

DoCmd.OutputTo acOutputQuery, "ExportCaptDataIG", acFormatXLS, cDlg.GetName

to export data but is limited to 65000 rows.
I use Access + Excel 2007. Any suggestions to workaround this to make it unlimited, pls?

[zmbd]

neelsfer,

The max that can be done is the 65000 - that is the limit of the number of rows in the newer versions of Excel.

Please do a search on this site... there has been a considerable amount of conversation concerning excel exports over the past two or three months.

[neelsfer]

i don't seem to find a solution on this site related to the 65000 limit for Access 2007

[zmbd]

I stand corrected....
- My apologies!

A friend of mine pointed out that ExcelV2007 the rows are the same as in v2010... (now why he was browsing the site I'll never know - he has always been gifted with the computers!) Excel 2007 Specifications and Limits

SO:
A tad more research and I've ran across the following support article: http://support.microsoft.com/kb/924681

Now this applies to the Ribbon from what I've read. I'll have to dig a little deeper for the VBA.

[zmbd]

Neelsfer:

Option Compare Database
Sub zj_excel_query2sheet_1() 
Dim fileName, qryout As String 
fileName = "C:\Documents and Settings\All Users\Workbook1.xlsx" 
qryout = "Query1" 
DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml, qryout, fileName, True 
End Sub 

A few things...
-- for the "filename" you might try leaving the ".xlsx" off so that, in this example, the name becomes just "Workbook1"
-- "qryout" this must be a "stored" query... that is it must be part of the querydefs collection. So you either need to have it defined by hand... or do as I do and create the querydef and add to the collection as needed for each query... not really all that hard.
-- the last entry "true" will place the field names to headers.
-- enter the code into a module and then [F1] for more information.

[NeoPa]

@Z
I came back to this and found that you'd already caught that the limit of 64K was removed for 2007.

On top of that you've provided another good answer.

Move along folks, nothing left to do here :-D