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

Posted on 2005-05-04
Last Modified: 2012-06-27
I ran a stored procedure to create a recordset and now I wanted to quickly transfer the result set of the query to an Excel report format which is the format of choice requested by the user. I am using Excel 2002. I encountered a Runtime Error as follows:

Do you know how I can resolve this ?

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

I get a run time error as follows:

Run-time error '-2147319779(8002801d')
method 'CopyFromRecordset'
of object 'Range' failed
Question by:zimmer9
    LVL 46

    Expert Comment

    Hi You'll need to post the whole procedure .

    For an example of CopyFromRecordset  you can look at

    Good Luck!


    Author Comment

    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
          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 = "@"
          End with
          DoCmd.Hourglass False
          objXL.Visible = True
          'Set RptYear = .CreateParameter("@RptYearS", adInteger, adParamInput, 4, intYearSPS)
          '.Parameters.Append RptYearS
    End Sub
    LVL 46

    Accepted Solution

     With objWS
            .Range(.Cells(2, 1)).CopyFromRecordset rstQueryFS

    Author Comment

    I tried:

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

    I get the following:

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

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now