Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1404
  • Last Modified:

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

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 = "@"
        .Cells.EntireColumn.AutoFit
End with

I get a run time error as follows:

Run-time error '-2147319779(8002801d')
method 'CopyFromRecordset'
of object 'Range' failed
0
zimmer9
Asked:
zimmer9
  • 2
  • 2
1 Solution
 
tbsgadiCommented:
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
0
 
zimmer9Author Commented:
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
0
 
tbsgadiCommented:
Try
 With objWS
        .Range(.Cells(2, 1)).CopyFromRecordset rstQueryFS
0
 
zimmer9Author Commented:
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now