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 )
[CODE=vb]
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_ta yi"

Else

ren = "s_table"

End If

While i > 0

DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel8, ren, "D:\export65k.x ls", -1

i = i - 65535

old = ren

n = n + 1

ren = "s_table" & n

DoCmd.Rename ren, acTable, old

Wend

End Sub

[/CODE]
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.SetWarnin gs False
DoCmd.OpenQuery "quakInits"
DoCmd.SetWarnin gs True

Set r = db.OpenRecordse t("tblInits")

Dim strPath As String
strPath = "\\01filpr004\o persvcs$\dimili ac\Production team\Training Classes\Trainin gTemplate.xls"

'Declare and Create an Excel Application
Dim xlApp As New Excel.Applicati on
Set xlApp = CreateObject("E xcel.Applicatio n")

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

xlApp.Range("A1 ").CopyFromReco rdset r

Dim strSavePath As String
Dim strFileNm As String
strSavePath = "Path of where the spreadsheet will be saved to"
xlApp.ActiveWor kbook.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.