Run-time error '2306'

[PB]

Hello,

When I attempt to output more than 9500 records to Excel,
I get the above error. The description is as follows:

There are too many rows to output, based on the limitation
specified by the output format or "My Access dbase name".

This is driving me crazy, so any help would be much
appreciated! I Am using Office 2000.

Cheers,

PB

 

[Ken Snell]

OutputTo (which is how ACCESS does an export if you use File | Export) is
limited to the old EXCEL 97 limits.

You'll need to write a macro or VBA code and use the TransferSpreadsheet
action. That will let you specify a newer version of EXCEL and therefore you
can export more data.

 

[Ken Snell]

By the way, I mistyped in my reply. It's not the EXCEL 97 limit, it's the
EXCEL 95 limit (16000 records, and some limit on total data size [don't
recall what it is off the top of my head]).

 

[Steven Burn]

Don't know if this helps you, but this is the VB code I wrote for one of my
programs to export to Excel (works with all versions of Excel). You should
be able to convert it to VBA.

' Begin Code
Dim Excel As Excel.Application ' This is the excel program
Dim ExcelWBk As Excel.Workbook ' This is the work book
Dim ExcelWS As Excel.Worksheet ' This is the sheet

Public Sub ExcelExport()

Set Excel = CreateObject("Excel.Application") ' Create Excel Object.

Set ExcelWBk = Excel.Workbooks.Add
Set ExcelWS = ExcelWBk.Worksheets(1)

Dim i As Integer
Dim row As Integer
Dim filename As String
filename = frmExport.txtFile.Text


row = 2

With frmsearchmp3.ListView1
'Add header
ExcelWS.Cells(1, 1) = "Artist/Band"
ExcelWS.Cells(1, 2) = "Title"
ExcelWS.Cells(1, 3) = "CD Code"
ExcelWS.Cells(1, 4) = "Path"
ExcelWS.Cells(1, 5) = "CD Serial"

For i = 1 To .ListItems.Count

ExcelWS.Cells(row, 1) = .ListItems(i).Text
ExcelWS.Cells(row, 2) = .ListItems(i).ListSubItems(1).Text
ExcelWS.Cells(row, 3) = .ListItems(i).ListSubItems(2).Text
ExcelWS.Cells(row, 4) = .ListItems(i).ListSubItems(3).Text
ExcelWS.Cells(row, 5) = .ListItems(i).ListSubItems(4).Text

DoEvents
row = row + 1

Next

ExcelWBk.SaveAs filename

End With

MsgBox "File Creation Complete", vbOKOnly + vbInformation, "MP3 Store"

ExcelWBk.Close
Excel.Quit

End Sub
'End Code
--
Regards

Steven Burn
Ur I.T. Mate Group CEO
www.it-mate.co.uk