Spreadsheet is full Vba error

[junior6202]

I have this Vba sub that will export a table from access 97 to excel. It works perfect but now the problem I am facing is in a .xls the max amount of rows are 65,536 rows and in this particular table I have over 200K is there a way to achieve this task. Any help will be appreciated.




Private Sub ExportOneTable()
'EXPORTS TABLE IN ACCESS DATABASE TO EXCEL'REFERENCE TO DAO IS REQUIRED
Dim strExcelFile As String
Dim strWorksheet As String
Dim strDB As String
Dim strTable As String
Dim objDB As Database
'Change Based on your needs, or use'as parameters to the
substrExcelFile = "g:\test\ASN_Report.xls"
strWorksheet = "DATA"
strDB = "P:Process\EDI\SR1-IA.mdb"
strTable = "Table5_Mod"

Set objDB = OpenDatabase(strDB)
'If excel file already exists, you can delete it here

If Dir(strExcelFile) <> "" Then Kill strExcelFile

objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
objDB.CloseSet objDB = Nothing

End Sub



Thank you,

Junior

[jonh]

Versons of Excel from 2007 support more rows, but why you'd ever need so much data in a spreadsheet is beyond me.