Export MS access to Excel

[alphy]

Hi,
I am using Office 2010(VB App in MS Access). the previous versions of MS acces had the limit of 16834 rows for exporting to excel.Is there any improvements for this in MSOffice 2010.
I need a work around for this limit. I cannot use the export option from file menu as I am using code to export.
Can anybody help me.

Regards
Alphy

[sierra7]

Hi
As far as I know Excel 2003 had a limit of 65536 rows. Excel 2010 has over a million; 1,048,576 rows.

How many rows do you want to export? You can copy row by row but that is slow. This put row headers in, changes column widths and font of headers, then copies the data.

Dim x1 As Object    '  OLE automation object
Dim Db As DAO.Database
Dim rst1 As DAO.Recordset

'first check that   C:\extract exists
If Dir("c:\extract\", vbDirectory) = "" Then
    MsgBox "Create folder  c:\Extract  for destination of spreadsheet files", vbCritical, _
        "Destination Folder Missing"
    Exit Sub

End If


'sngStart = Timer
Set x1 = CreateObject("Excel.sheet")

x1.Application.range("A1:G1").Font.Size = 14
x1.Application.range("A1:G1").Font.Bold = True

x1.Application.cells(1, 1).Value = "GAC"
x1.Application.cells(1, 2).Value = "Description 1"
x1.Application.columns(2).ColumnWidth = 30
x1.Application.columns(3).ColumnWidth = 30
x1.Application.columns(4).ColumnWidth = 30
x1.Application.columns(5).ColumnWidth = 30
x1.Application.cells(1, 3).Value = "Description 2"
x1.Application.cells(1, 4).Value = "Manufacturer"
x1.Application.cells(1, 5).Value = "Location"
x1.Application.cells(1, 6).Value = "Due Date"
x1.Application.cells(1, 7).Value = "Last Date"


'open the database
Set Db = CurrentDb
Set rst1 = Db.OpenRecordset("qryExport", dbOpenSnapshot)

    rst1.MoveLast
    rst1.MoveFirst

    x1.Application.cells(2, 1).copyfromrecordset rst1

    x1.saveas "C:\Export\ExportedData.xls"         

'sngEnd = Timer                              ' Get end time.
'sngElapsed = Format(sngEnd - sngStart, "0.000") ' Elapsed time.
'MsgBox "The data took " & sngElapsed _
'       & " seconds to export."

MsgBox "Spreadsheet has been created", vbInformation, "Finished"

'cleanup
Set x1 = Nothing
Set rst1 = Nothing
Set Db = Nothing

[NeoPa]

Alphy:
I cannot use the export option from file menu as I am using code to export.

On the contrary. There is very little done via the interface that cannot be equally well done from code. Exporting is no exception. If you look up TransferSpreads heet() in the Help System (Using Context-Sensitive Help) you'll find all you need for that.