CopyFromRecordset Run-time error '-2147319779(8002801d')

Hi You'll need to post the whole procedure .

For an example of CopyFromRecordset  you can look at
http://www.mvps.org/access/modules/mdl0035.htm

Good Luck!

Gary

The whole procedure is 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 tried:

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

I get the following:

Runtime error '1004';
Method 'Range' of object '_worksheet' failed