Export to excel issues

[sabavno]

Hello,

I have two questions. First, is there a way to export different tables into one Excel workbook, so that each table ends up being in the separate sheets?

Second question, the limit for maximum number of records in Excel 2000 is something around 65000. I use Access 97, but export to Excel 2000, using this statement below:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "tblAutoPassExport","C:\File.xls",True

Only 16000 records get exported though.

Please comment on these issues.

Will appreciate any help

THanks.

 

[ant33]

Try something like:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Table1", "C:\Temp\Test.xls", True

to create the workbook with a worksheet called "Table1", then:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Table2", "C:\Temp\Test.xls", True

to append the worksheet "Table2"

 

[sabavno]

Thanks
But it didn't work

 

[EliseFreedman]

I use Excel VBA Code to import tables from Access into Excel. A pretty good book on the subject is Excel 2000 VBA Programmers Reference (wrox). Below is a sample of the code I use.

'copy table from MS Access to New Worksheet
Dim daoapp As DAO.DBEngine
Dim dbsales As DAO.Database
Dim rssales As DAO.Recordset
Dim i As Integer
Dim wks As Worksheet
Dim icount As Integer
Dim rng As Range

' establish link to table through DAO
Set daoapp = New DAO.DBEngine
Set dbsales = daoapp.OpenDatabase("C:\sales info\SalesbySalesmen.mdb&quot
Set rssales = dbsales.OpenRecordset("ReportSalesbySalesman&quot
Set wks = Worksheets.Add

icount = rssales.Fields.Count
For i = 0 To icount - 1
wks.Cells(1, i + 1).Value = rssales.Fields(i).Name
Next

'copy entire recordset to worksheet starting in A1
wks.Range("A3&quot.CopyFromRecordset rssales



'close recordsets
Set rssales = Nothing
Set dbsales = Nothing
Set daoapp = Nothing


'import 2nd table
Set daoapp = New DAO.DBEngine
Set dbsales = daoapp.OpenDatabase("C:\sales info\hamble_be.mdb&quot
Set rssales = dbsales.OpenRecordset("reps&quot
Set wks = Worksheets.Add

icount = rssales.Fields.Count
For i = 0 To icount - 1
wks.Cells(1, i + 1).Value = rssales.Fields(i).Name
Next

'copy entire recordset to worksheet starting in A1
wks.Range("A3&quot.CopyFromRecordset rssales


'close recordsets
Set rssales = Nothing
Set dbsales = Nothing
Set daoapp = Nothing
End Sub