What is the best/Fastest way to transfer data from Access to Excel? I have a database in which a query returns approx. 20,000 records. I would like to send this to excel but have found that the transfer is slow. Any suggestions? I have use the Transferspreadsheet Method and I have a module that will transfer the data also:
Set db = CurrentDb
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set objActiveWkb = objXL.Application.ActiveWorkbook
strSQL = "SELECT Invoice_Matching.* FROM Invoice_Matching;"
In my experience, the code method is the slowest, especially if Excel is visible.
Using a a built in "Export to Excel" has proven to be the fastest for me.
I have to agree with Teddy and scripts82.
How much memory you have (both physical and virtual) will affect the export speed for larger sets of data. Bottom line is, 20K records is more than a few and patience may be your only option when dumping to Excel.