Excel/CopyFromRecordset Method/Run-time error '-2147319779(8002801d')

[GarHeard]

I noticed the following on an MSDN website:

CopyFromRecordset Method

Copies the contents of an ADO or DAO Recordset object onto a worksheet, beginning at the upper-left corner of the specified range. If the Recordset object contains fields with OLE objects in them, this method fails.

I have an Access application and after executing a stored procedure and creating a recordset, I tried to use the CopyRecordSet in a procedure as follows:
----------------------------------------------------------
Private Sub FallUDQuery()
Dim strNextFile As String
Dim rstQueryFS As ADODB.Recordset
Dim objXL As Excel.Application
Dim objWS As Excel.Worksheet
Dim fld As ADODB.Field
Dim intCol As Integer
Dim intRow As Integer
Dim strSQL As String
Dim intBonds As Integer
Dim com As ADODB.Command
Dim cn As ADODB.Connection
Dim P1 As New Parameter

Set cn = New ADODB.Connection

cn.Open "Provider=SQLOLEDB.1;" & _
"Data Source=B; Initial Catalog=XXX;" & _
"User ID =XXXClient;PWD="

Set com = New ADODB.Command

With com
Set .ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "dbo.procUDFl"
.Parameters.Append .CreateParameter("@prmSQL", adVarChar, adParamInput, 8000, strSQLFS)
.Parameters.Append .CreateParameter("@RptYearF", adInteger, adParamInput, 4, intYearSP)
'.Parameters.Append .CreateParameter("@RptYearS", adInteger, adParamInput, 4, intYearSPS)
Set rstQueryFS = .Execute
End With


Set objXL = New Excel.Application
objXL.Workbooks.Add
strNextFile = GetNextFileName("C:\USERDEF1.XLS")
objXL.ActiveWorkbook.SaveAs strNextFile
Set objWS = objXL.ActiveSheet
objXL.ActiveSheet.Protect UserInterfaceOnly:=True

For intCol = 0 To rstQueryFS.Fields.Count - 1
Set fld = rstQueryFS.Fields(intCol)
objWS.Cells(1, intCol + 1) = fld.Name
Next intCol

intRow = 2
With objWS
.Cells(2, 1).CopyFromRecordset rstQueryFS
.Columns(15).NumberFormat = "000-00-0000"
.Columns(18).NumberFormat = "#0.000"
.Columns(24).NumberFormat = "@"
.Cells.EntireColumn.AutoFit
End with
DoCmd.Hourglass False

objXL.Visible = True
'Set RptYear = .CreateParameter("@RptYearS", adInteger, adParamInput, 4, intYearSPS)
'.Parameters.Append RptYearS

End Sub
----------------------------------------------------------

I get a run time error as follows:


Run-time error '-2147319779(8002801d')
method 'CopyFromRecordset'
of object 'Range' failed

Do you possibly know why I get this error? I am using Excel 2002. I don't think have any OLE objects types or array data. I'm just running a stored procedure (querying
a couple of tables).

I am trying to find a way to speed up the processing
of sending a recordset to an Excel file which is the format in which the user wants the output.

 

[RoyVidar]

This would probably fit better in the Access VBA or Access Other topic forums...

The CopyFromRecordset method, is a method of the Excel Range object;-)

I think this might be an automation error. What usually causes that, is implicit references to Excel objects, methods and properties - and not closing/releasing the object variables.

Here, I'd have a look at the instantiation:

[tt]' additional declaration
dim wrk as excel.workbook

Set objXL = createobject("Excel.Application")
set wrk = objXL.Workbooks.Add
strNextFile = GetNextFileName("C:\USERDEF1.XLS")
wrk.SaveAs strNextFile
' I'm not to fond of this one, either,
' Set objWS = objXL.ActiveSheet
' and prefer something like this
set objWS = wrk.sheets(1)
objWS.Protect UserInterfaceOnly:=True[/tt]

Then at the bottom, release the object variables - you may want to save the workbook again, too?
[tt]
wrk.save
doevents
set objWS=nothing
set wrk = nothing
set objXL=nothing[/tt]

Sometimes, using With blocks in automation also fails
[tt]
objWS.Cells(2, 1).CopyFromRecordset rstQueryFS
objWS.Columns(15).NumberFormat = "000-00-0000"
objWS.Columns(18).NumberFormat = "#0.000"
objWS.Columns(24).NumberFormat = "@"
objWS.Cells.EntireColumn.AutoFit
[/tt]
I'm not sure, but an additional thingie for the copyfromrecordset thingie, might be to try:

[tt]objWS.range(objWS.Cells(2, 1),objWS.Cells(2, 1)).CopyFromRecordset rstQueryFS[/tt]

Roy-Vidar