Export more than 65000 records to excel (1 Viewer)

machumpion

Registered User.
Local time
Today, 07:28
Joined
May 26, 2016
Messages
93
I'm trying to export more than 65,000 lines from an access query to excel.

Here is what I tried;

External data->export->excel -> uncheck "export data with formatting and layout"; format: xlsx, filename: C:\Users\Jimbo\Desktop\Query1.xlsx

Upon running it, I get a prompt:

"the microsoft access database engine could not find the object "Query1". Make sure the object exists and that you spell its name and path name correctly. If Query` is not a local object, check your network connection or contact the server administrator"

How can I export more than 65,000 lines to excel?(without splitting the data into smaller parts):banghead:
 

Minty

AWF VIP
Local time
Today, 12:28
Joined
Jul 26, 2013
Messages
7,255
Not sure what the issue is - I have just followed you exact steps with a table containing over 290,000 records and it exported fine. (A little slowly obviously)

If you have file already called Query1.xlsx i would delete it or rename it and try again.
Access does some weird stuff with excel exports - it sort of refills the file if it exists rather than deleting it and recreating it.
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 06:28
Joined
Feb 28, 2001
Messages
17,720
That name makes me pause. I always thought that in order to do an export, you have to save the query first so that you can select it from the navigation panel. Query1 is the name of the first (default) query you create before you rename it. Has that query been saved yet?
 

machumpion

Registered User.
Local time
Today, 07:28
Joined
May 26, 2016
Messages
93
Th Doc Man is totally right. Saving it does the trick. The tutorials I read didn't specify to save it first. Thanks Doc!
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 06:28
Joined
Feb 28, 2001
Messages
17,720
No problem, glad to help.
 

machumpion

Registered User.
Local time
Today, 07:28
Joined
May 26, 2016
Messages
93
For some strange reason, it no longer exports more than ~65,000 records anymore.

I'm exporting a saved query to excel and unchecked the formatting box.
 

Minty

AWF VIP
Local time
Today, 12:28
Joined
Jul 26, 2013
Messages
7,255
If you perform and then save any calculations on the exported spreadsheet, and leave them in place, when the next export run if it tries to write over those rows where the calculations are stored it will error out.

Remember what I said about the export not deleting and recreating the spreadsheet. This is one of the bugs of this feature. Rename the file if you want to do stuff to it.
 

LiteSh Chaudhari

New member
Local time
Today, 13:28
Joined
Feb 7, 2020
Messages
1
Hello,

It's late reply but may be it will be helpful to others like me,

I was getting the same error. Then I just saved the query in MS Access and then tried to export it without formatting and it worked for me.
 

zeroaccess

Active member
Local time
Today, 06:28
Joined
Jan 30, 2020
Messages
599
For some strange reason, it no longer exports more than ~65,000 records anymore.

I'm exporting a saved query to excel and unchecked the formatting box.
Not sure if this was solved, but 65,000 is the maximum number of records in the old Excel 97 - Excel 2003 Workbook (*.xls) format. Ensure that the export is using the latest version (Excel Workbook (*.xlsx)).

If choosing a query from a combo box and using a macro on click of a command button, it could look like this:

Query Macro.png


The VBA alternative would be:

Code:
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml

KillFile was needed because when outputting to the same file again and again, I wanted to suppress the messages from Windows about the file already existing. In a module:

Code:
Function KillFile()
On Error GoTo ErrKillFile

    Dim Killfile
 
    Killfile = "C:\Temp\Query.xlsx"
 
    If Len(Dir$("C:\Temp\Query.xlsx")) > 0 Then
        SetAttr Killfile, vbNormal
        Kill Killfile
    End If
    Exit Function
 
ErrKillFile:
    If Err <> 0 Then
        Beep
        MsgBox "Please close the existing query before opening another.", vbInformation
        DoCmd.SetWarnings False
        Exit Function
    End If
        DoCmd.SetWarnings True
End Function
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom