question

TomKosel-0984 avatar image
0 Votes"
TomKosel-0984 asked KenSheridan-7466 answered

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

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.

office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KenSheridan-7466 avatar image
1 Vote"
KenSheridan-7466 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KenSheridan-7466 avatar image
1 Vote"
KenSheridan-7466 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomKosel-0984 avatar image
0 Votes"
TomKosel-0984 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.