Exporting large dataset from Access to Excel

Connect to the query in Excel so that it pulls the data straight in rather than pushing the query data from Access.

On Data Tab > Get External Data Group > From Access - go through wizard to connect to Query in Access file.

I agree with @Bob. but just thinking.... considering Excel spreadsheet limitations... should you perhaps rather not be keeping this data in Access as is?

What version of Office are you using; later versions will accept far more than the 64K limit.

For example, I have Access 2007 and the new Excel worksheet I just opened has over 1M rows.  But I'm with Ste5an, I generally push data to Excel using automation.  Note his code in rows 19-22, when you use automation and the CopyFromRecordset method, it does not automatically past the column headers, so that is what he is doing in that code segment.

With Automation, you can also size the columns and set the column formats.

+1 for Rob's comment. No code to maintain and you can refresh data without rerunning code.

It has been a while since I've tried this, but I believe that if the Access query contains any User Defined Function calls, then you will not be able to pull the query results to Excel using the method mentioned above.  Whereas the automation process does push the results to Excel.

I use TransferSpreadsheet for huge recordsets - hundreds of thousands of rows.  Just make sure you are using the correct argument for Excel.
docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml,"query or tablename","target file name",True

The newest version is acSpreadseetTypeExcel2XML  (note the XML suffix, the option without the suffix creates a binary format workbook which is non-standard).

I'm with @fyed and @st3fan
Workbook.Sheets(1).Range("A2").CopyFromRecordset DataSource
I can tweak the column heads and formats, and format the sheet because I have it automated.
And I can do/add extra columns and/or totals as needed and fit the columns

'Format the header row as bold and autofit the columns
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
End With

Altogether, I can create a more seamless experience for the end user, and the code is NOT in Excel, so for repetitive actions, this is nicer, too.

Given that I work across multiple versions, the SaveAs is where life gets tricky :(
Or you throw control of the completed book to the user and they handle that.

It's imho a good idea to let the uses decide, whether you save it as file or not. This helps teaching the users to rely on actual data :)

btw, I don't format the Excel documents in Access. I use template Excel files, which have a default macro named FormatData. Thus my code calls this. So that the users can edit the formatting for their needs.

Thanks Everyone,

Office 2010.

Transferspreadsheet was the code I was using and between the access query and the spreadsheet I am losing records.  This was using acSpreadsheetTypeExcel12Xml (Pat, was acSpreadsheetTypeExcel2Xml in your second part a typo?)

I am now pulling it from Access in Excel but this seems to take longer. (After getting the data in Excel, there are a load of pivot tables and pivot charts to update too).

With pivot tables you can set an external data source. This might enable you to skip the import step altogether.

Looks like I left off the 1.  Should be

acSpreadsheetTypeExcel12Xml

I also find the export from Access faster than the import from Excel for large recordsets.

Thanks Everyone,

I've gone for CopyRecordset in the end.  

The only downside is that you don't get a progress bar like you do with TransferSpreadsheet.  I'll have to create one myself.

Pulling the data into Excel was just too slow, and then the pivot tables had to be refreshed, so it was just a bit awkward for the user.

All my code is in Access and so the user just clicks a single button and away it all runs.

Cheers

LJM