What is the best way to export a lot of query data from access to exel

Question

I have a large access Query dataset (263,000+ records) that I want to export Excel. It seems that I can only export 62,000 records to go using : "DoCmd.OutputTo acOutputQuery, "Unfiltered Master Query", acFormatXLS".
Is there a better method? I would like the user to be prompted for a filename and location. I would also like to have it export in xlsx format.

Answer 1

You'd need to build a dialogue form in which the user can browse to a folder and enter the file name. First add a little function to a standard module, e.g.

Public Function BrowseFile() As String

Dim strFile As String

With Application.FileDialog(2)
    .Title = "Select folder and enter file name"
    .InitialFileName = "*.xlxs"
    If .Show Then
        strFile = .SelectedItems(1)
    Else
        MsgBox "No file selected", vbInformation
        Exit Function
    End If
End With

BrowseFile = strFile
    

End Function

Then with a button in the dialog form call the BrowseFile() function and build the line to call the TransferSpreadsheet method, assigning the BrowseFile function's return value to the FileName argument of the method.

Answer 2

Try:

https://learn.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet?f1url=%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vbaac10.chm4189);k(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue

Answer 3

Ken,
As usual, thanks for your helpful response. I got that to work except for one thing. I want the user to be prompted for the file location and name. According to the information in your link, Filename is optional. I used to use "DoCmd.TransferSpreadsheet aceport, acSpreadsheetTypeExcel9, ":unfiltered master Query", , -1" and it would open a browser to let me put it where I want. Now it would appear I have to specify a location and filename.

I can do that, so it will be solved, but just wondering by they say filename is optional, but if you leave it out, it tells you the argument is required.