Export more than 65000 records to excel

[machumpion]

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]

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]

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]

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]

No problem, glad to help.

 

[machumpion]

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]

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]

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]

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:

The VBA alternative would be:

    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:

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

 

[famiramadhan]

Hi iam still using ms.acces 97 haha lol, and i had case like this thread.. so what i did to get the all data is:

1. Export database to Textfile - iam recommended separate with semicolon (i have to try separate with comma but errors happen), and then
2. open textfile via excel.. and you delimited with semicolon
3. Finish - your data ready to be analyze

oh ya, in my case i just have 410.000 records.. i really don't know if your data more than me and still didn't get all your data.
if you still using access 97 - Highfive with me haha
good luck.