Exporting a Large Recordset to Excel

[jason_kelly]

Hi There,

I need your help.

I am using the following code below to export data from my recordset to an excel spreadsheet. Notably, it works for a rather small recordset, however fails when other recordsets are fairly large with the following error:

Run-time error '-2147467259 (800040005)':
Method 'CopyFromRecordset' of object 'Range' failed



'-------------------------------------------------------
Public Sub ExportTOExcel()
'-------------------------------------------------------
Dim oApp As Object
Dim oWB As Object
Dim I As Integer


Set oApp = CreateObject("Excel.Application")
oApp.Visible = False
Set oWB = oApp.Workbooks.Add

FullFileName = Application.GetSaveAsFilename("Export.xls", _
"Excel file (*.xls),*.xls", 1, frmSplash.IMTS_Caption & " - Export to")

If FullFileName <> False Then

For I = 0 To rs.Fields.Count - 1
oWB.Sheets(1).Cells(1, I + 1).Value = rs.Fields(I).Name
Next
oWB.Sheets(1).Range("1:1").Font.Bold = True
oWB.Sheets(1).Cells(2, 1).CopyFromRecordset rs
oApp.Selection.CurrentRegion.RowHeight = 11
oApp.Selection.CurrentRegion.Font.Name = tahoma
oApp.Selection.CurrentRegion.Font.Size = 8

oWB.SaveAs (FullFileName)
oWB.Close
Set oWB = Nothing
oApp.Quit
Set oApp = Nothing

Else
Exit Sub

End If

End Sub



Any help with this is greatly appreciated.

Cheers,

Jay

[OBP]

Jay, are you exceeding the Excel limit of Columns, which is 256 for up to Excel 2003.