Recordset Error

[RPrasad]

Hi,

I am trying to use this code to export data to excel and do some formating there on, however only 1st line of data is getting exported and showing the debug error and the line highlighted in yellow is shown below. Please help me to fix this. Thank you

xl.range("A2").copyfromrecordset RST

Full code I used is here

Function ExportToExcel()
 
'Declaring variables and objects
Dim RST As DAO.Recordset
Dim xl As Object
Dim SQLText As String
 
'start the excel
Set xl = CreateObject("excel.application")
 
'start new workbook, add worksheet
xl.workbooks.Add
xl.worksheets.Add
xl.activesheet.Name = "Test"
xl.Visible = True
 
'Define the recordset and openit
SQLText = "select * from [tbldata]"
Set RST = CurrentDb.OpenRecordset(SQLText)
 
'copy the records to excel
With xlsheet
xl.range("A2").copyfromrecordset RST 'getting error in this line
End With
 
'close recordset
RST.Close
 
End Function
 
'from here excel formatting goes on....

 

[Will_B]

It turns out the problem is Excel related, not Access.

When using the range object, you must specify the worksheet, in this case, "Test".

Try this: xl.Worksheets("Test").range("A2").copyfromrecordset RST

 

[Will_B]

Let me revise that: try this WITHOUT the With...End around it...

xl.Worksheets("Test").range("A2").copyfromrecordset RST

 

[RoryA]

You can also get the error if you have long text fields (more than about 911 characters I think) or errors in the recordset (with DAO at any rate).