export access to excel

Best way is to create a recordset based on x number of records (say the first 50,000) and then use the recordset method of .copyfromrecordset to paste it into excel.

Then re-create your recordset based on the next 50,000 records and .copyfromrecordset into the second sheet

and so on....

Here's some code that'll create a new workbook and export all records. The problem with this though is it will take a loooooong time to export 600000 records.

[Note: see comments for options]

Sub ExportToExcel()
Dim wb As Workbook, ws As String, xlApp As Object
Dim rs As DAO.Recordset
Dim r As Long, c As Long, s As Variant
Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = False
Set rs = CurrentDb.OpenRecordset("Data", dbOpenTable)' <<Change "Data" to your Table/Query name
Set wb = xlApp.Workbooks.Add
c = 1
r = 1
wb.Worksheets.Add
wb.ActiveSheet.Name = "Data" & c
ws = wb.ActiveSheet.Name
Do Until rs.EOF
'Add as many fields as required. You can also change the rs.Fields(#) to rs.Fields("FieldName"), where
 "FieldName" is the name of each field.

wb.Worksheets(ws).Cells(r, 1).Value = rs.Fields(1).Value
wb.Worksheets(ws).Cells(r, 2).Value = rs.Fields(2).Value
wb.Worksheets(ws).Cells(r, 3).Value = rs.Fields(3).Value
wb.Worksheets(ws).Cells(r, 4).Value = rs.Fields(4).Value
wb.Worksheets(ws).Cells(r, 5).Value = rs.Fields(5).Value
wb.Worksheets(ws).Cells(r, 6).Value = rs.Fields(6).Value
r = r + 1
If r = 65537 Then
c = c + 1
wb.Worksheets.Add
wb.ActiveSheet.Name = "Data" & c
ws = wb.ActiveSheet.Name
r = 1
End If
rs.MoveNext
Loop

For Each s In wb.Worksheets
If Left(s.Name, 4) <> "Data" Then s.Delete
Next

wb.SaveAs "Path\WorkBookName.xls"'<<Modify "Path" and "WorkBookName.xls" as required.
xlApp.Quit
End Sub

Regards,

Wayne

I just did a quick test on my computer (Access 2003) and it took 7 seconds to export 255 records. That works out to about 4 and a half hours for 600000 records :(

If you are going to be doing this regularly, DON'T use my method. In fact, I suggest another method altogether.

Wayne.

Looping through each cell is just about the slowest method

As far as I know, .copyfromrecordset is the fastest way....

Here is some code to get you started:

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim intColIndex As Integer
Dim varTbls As Variant
Dim Counter As Integer
Dim strSQL As String

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
       
'Create connection
    Set cnn = CurrentProject.Connection
    'cnn.Open
   
'Get Excel ready
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open("\\path\to\excelworkbook\WorkbookName.xls")
    xlApp.Visible = False 'optional, can be set to true if you want
     
'Process recordset
        Set rs = New ADODB.Recordset
        With rs
            .ActiveConnection = cnn
            .Source = "SELECT * from tblYourTable" ' You will have to modify this
            .LockType = adLockOptimistic
            .CursorType = adOpenKeyset
            .Open
        End With
   
    'Activate the correct Excel worksheet
        Set xlSheet = xlBook.Worksheets("SheetName")
        xlSheet.Visible = True
        xlSheet.Select
   
'    'Uncomment this section if we want field names in Excel
'            xlSheet.Range("A1").Select
'            For intColIndex = 0 To rs.Fields.Count - 1
'                xlSheet.Range("A1").Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
'            Next
'            xlSheet.ActiveCell.Offset(1, 0).CopyFromRecordset rs
   
    'Insert the recordset data
        xlSheet.Range("A2").CopyFromRecordset rs
        xlSheet.Visible = False 'Optional

    'Tidy up
        rs.Close
        Set rs = Nothing
         cnn.Close
        Set cnn = Nothing
        set xlApp =nothing
        set xlBook =nothing
       set xlSheet =nothing


All you need to do is create a loop to filter out the 50,000 records at a time and paste into the next workbook

Last line should read
All you need to do is create a loop to filter out the 50,000 records at a time and paste into the next ***sheet***

I write all my code in the sql editor in Access. Where does this code go?

this needs to go in a form's module. Its vba code.

If you aren't absolutely worried about prettines and formatting.

Make a set of queries
---------------------------------------
Select *
from TableName
where index_num > 1
and index_num < 50000
---------------------------------------
Select *
from TableName
where index_num > 50001
and index_num < 100000
---------------------------------------
and then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QueryName_50000", "c:\temp\MySprdsht.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QueryName_10000", "c:\temp\MySprdsht.xls", True

chip0312,

Just a quick question.

Why can't you use Access instead of Excel, to store the 600000 records?