Access (from Office 2016) saving output as worksheet limited to 65,536 rows

[Err]

Hi,

I'm using the following VBA command to save my query results to an Excel file:

DoCmd.OutputTo acOutputQuery, "My_Query_Results", "Excel Workbook (*.xlsx)", "C:\Query_Results\My_Query_Results.xlsx", False, "", 1, acExportQualityScreen

When the results are over 65,536 rows then it only saves the top 65,536 and worse -the file seems corrupted when Excel VBA attempts to open it. Excel VBA doesn't have a problem opening this file when the initial Access results are less than 65536 rows.

In both cases, (Excel and Access) the version is 2016 and ought to be able to handle these number of rows.

Thanks.

 

[Roxxien]

I see too possible problem with that number of row.

The first is the extension of your Excel, as you save it in xlsx, but shouldn't be a problem.

The second is a possible integer type. Integer have a limit of 65536. You might to verify what kind of data you have in your Access table.

Edit, not sure of my number for Integer ...

 

[xenou]

I don't see "Excel Workbook (*.xlsx)" as being a valid argument for output format anywhere in documentation. Try instead: acFormatXLSX

DoCmd.OutputTo acOutputQuery, "My_Query_Results", acFormatXLSX, "C:\Query_Results\My_Query_Results.xlsx", False, "", 1, acExportQualityScreen

I can't really vouch for the other optional arguments either. Unless you have a particular reason for including them I'd prefer:

DoCmd.OutputTo acOutputQuery, "My_Query_Results", acFormatXLSX, "C:\Query_Results\My_Query_Results.xlsx"

 

[Err]

Hi,

Thanks for your help.

Unfortunately, I tried this with 646, 361 rows and it still made a Worksheet, stopping at 65,536 rows:

I took a screen shot but the img tags aren't working anymore:

http://i.imgur.com/3TT054Q.png

 

[xenou]

What exactly did you try?

 

[Err]

What exactly did you try?

DoCmd.OutputTo acOutputQuery, "My_Query_Results", acFormatXLSX, "C:\Query_Results\My_Query_Results.xlsx"

The script passes a TSQL string to the server and opens a datasheet:

DoCmd.OpenQuery "My_Query_Results", acViewNormal, acReadOnly

In this case the query that returns 73,217 rows

http://i.imgur.com/ih04GzA.png

When I open the file that it's trying to save it only saves 65535 rows

http://i.imgur.com/7VXpSbo.png

I can verify the KEY in the source table:

http://i.imgur.com/O0x6mxK.png


A google search yields that I am not the only one with this problem. Another board suggested using

DoCmd.TransferSpreadsheet

instead.

 

[xenou]

The script passes a TSQL string to the server

What does this mean? What script?

Post all relevant information!

 

[Err]

What does this mean? What script?

Post all relevant information!

Here's the relevant code that fixed the problem:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "My_Query_Results", "C:\Query_Results\My_Query_Results.xlsx", 1

 

[Roxxien]

Does the place where you find your solution gave an explanation for that problem?

 

[Err]

Does the place where you find your solution gave an explanation for that problem?

As far as I can tell it's a bug within Access. There are numerous sources for this:

DoCmd.TransferSpreadsheet acExport DoCmd.OutputTo acOutputQuery 65536 - Google Search