By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,398 Members | 1,362 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,398 IT Pros & Developers. It's quick & easy.

Exporting more than 65536 records to excel from access table

aryanraj
P: 4
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 )
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database 
  2.  
  3. Dim i As Long 
  4.  
  5. Dim ren, old As String 
  6.  
  7. Dim n As Integer 
  8.  
  9. Private Sub exportt_Click() 
  10.  
  11. n = 1 
  12.  
  13. i = DCount("*", "s_table") 
  14. MsgBox i 
  15.  
  16. If i > 65535 Then 
  17.  
  18. ren = "s_table" & n 
  19.  
  20. DoCmd.Rename ren, acTable, "s_table_tayi" 
  21.  
  22. Else 
  23.  
  24. ren = "s_table" 
  25.  
  26. End If 
  27.  
  28. While i > 0 
  29.  
  30. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, ren, "D:\export65k.xls", -1 
  31.  
  32. i = i - 65535 
  33.  
  34. old = ren 
  35.  
  36. n = n + 1 
  37.  
  38. ren = "s_table" & n 
  39.  
  40. DoCmd.Rename ren, acTable, old 
  41.  
  42. Wend 
  43.  
  44. End Sub
  45.  
  46.  
Thanks in Advance,
Aryan
Feb 25 '08 #1
Share this Question
Share on Google+
3 Replies


cori25
P: 83
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]
Feb 25 '08 #2

FishVal
Expert 2.5K+
P: 2,653
Hi, Aryan.

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

Regards,
Fish.
Feb 25 '08 #3

NeoPa
Expert Mod 15k+
P: 31,768
FYI:
Excel worksheets can only hold a maximum of 65,536 rows.
Any more than that will always fail to export.
Feb 26 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.