there are too many rows to output based on the limitation 1

[MKH2001]

I have an issue exporting the results of a query to xls file.

Seems to occur when query exceeds 17000ish lines otherwise functions fine.

 DoCmd.OutputTo acOutputQuery, "qry_DetailsByRegime", acFormatXLS, "C:\Documents and Settings\" & NetworkUserName() & "\DetailsByRegimeExport" & Format(Now, "dd-mm-yyyy (hh-nn-ss)") & ".xls"

Thought the issue might be xls own native restiction (65000ish lines) but this issue occurs way before breaching such.

Any ideas what the problem is and what the solution or alternative might be?

 

[randy700]

Have you tried using TransferSpreadsheet instead of OutputTo ?


Randy

 

[MKH2001]

But that would mean having to alter the query to a make table and then having to export resultant table?

Anyway to cut out the make table and transfer the query results on a fly.

Also I understand that transferspreadsheet is limited by the 65000ish restriction of excel. Is there anyway of overcoming this such as a dbf format or csv or are the all restricted in this manner?

Mark

 

[MajP]

Only excel is limited. You can either
1. Get Excel 2007 not limited to 65k
2. Export into another format
3. Export the first 65 to sheet 1, next 65 to sheet 2, next 65 to sheet 3...

 

[MajP]

In 2007 the limit is 1.04M rows

 

[randy700]

But that would mean having to alter the query to a make table and then having to export resultant table?

Why? You can use a query as well as a table in the TransferSpreadsheet method.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_DetailsByRegime", _
        "C:\Documents and Settings\" & NetworkUserName() & "\DetailsByRegimeExport" & _
        Format(Now, "dd-mm-yyyy (hh-nn-ss)") & ".xls"

Randy

 

[MKH2001]

DoCmd.OpenQuery "mktblqry_DetailsByRegime", acNormal, acEdit
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "mktbl_DetailsByRegime", "C:\Documents and Settings\" & NetworkUserName() & "\DetailsByRegimeExport" & Format(Now, "dd-mm-yyyy (hh-nn-ss)") & ".xls", True

But despite setting the [HASFIELDNAME]true as above does not create headings.

Any ideas what I have done wrong? they are headings in original tables but export puts them in row 1 instead.

 

[randy700]

they are headings in original tables but export puts them in row 1 instead

You are putting them in an Excel spreadsheet.
Where do you expect the headings to appear?


Randy

 

[MKH2001]

Ummmm.....

Your right I am so used to db format tables with field headers defined, there is no way of replacing A, B, C etc in Excel.

One of my issues using the transferspreadsheet method of export is IF it exceeds the 65000ish rows it does not warn the user.... the dataset will just be cut off.

I guess either during the make table or prior to actual export it makes a count of the query or table rows and warns the user that it will exceed.

Strange doesn't warn you since it does using the Output to.

Mark

 

[randy700]

It shouldn't be hard to get a count of the rows before running the TransferSpreadsheet method and create your own warning.


Randy