Exporting more than 65536 records to excel from access table

[aryanraj]

Hi all,

I wanted to export around 300000 records that are present in my MS Access 2003 version table to excelsheet version8.
I had written a code to export to multiple sheets in one workbook, but i failed to export the continous records.
The code is exporting only the first 65536 records to multiple sheets.
I want to find a way for this.
--I tried to include an auto number field to my table but it was not successful.
--I tried to give range to export but it is not supported.
Code: ( text )

Option Compare Database 
 
Dim i As Long 
 
Dim ren, old As String 
 
Dim n As Integer 
 
Private Sub exportt_Click() 
 
n = 1 
 
i = DCount("*", "s_table") 
MsgBox i 
 
If i > 65535 Then 
 
ren = "s_table" & n 
 
DoCmd.Rename ren, acTable, "s_table_tayi" 
 
Else 
 
ren = "s_table" 
 
End If 
 
While i > 0 
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, ren, "D:\export65k.xls", -1 
 
i = i - 65535 
 
old = ren 
 
n = n + 1 
 
ren = "s_table" & n 
 
DoCmd.Rename ren, acTable, old 
 
Wend 
 
End Sub
 
 

Thanks in Advance,
Aryan

[cori25]

Option Compare Database
Option Explicit

Function ExcelExport()

[PHP]Dim db As DAO.Database
'Set Db to equal your current database
Set db = CurrentDb

Dim r As DAO.Recordset

DoCmd.SetWarnings False
DoCmd.OpenQuery "quakInits"
DoCmd.SetWarnings True

Set r = db.OpenRecordset("tblInits")

Dim strPath As String
strPath = "\\01filpr004\opersvcs$\dimiliac\Production team\Training Classes\TrainingTemplate.xls"

'Declare and Create an Excel Application
Dim xlApp As New Excel.Application
Set xlApp = CreateObject("Excel.Application")

xlApp.Workbooks.Open FileName:="" & strPath & ""
xlApp.Visible = True

xlApp.Range("A1").CopyFromRecordset r

Dim strSavePath As String
Dim strFileNm As String
strSavePath = "Path of where the spreadsheet will be saved to"
xlApp.ActiveWorkbook.SaveAs strSavePath

xlApp.Quit
Set xlApp = Nothing

End Function [/PHP]

[FishVal]

Hi, Aryan.

You may also take a look at a similar thread: Exporting huge records to Excel from Access

Regards,
Fish.

[NeoPa]

FYI:
Excel worksheets can only hold a maximum of 65,536 rows.
Any more than that will always fail to export.