How can I copy a table result from MS SQL stored procedure to excel?

I have a procedure that returns me a table with 2 columns and 12 rows
How can I pass this table to excel using macro. See below my code.
It is giving me an error: "Operation is not allowed when the object is closed"
Please if you can help me it will be great!
thanks,
Caliu
Public Sub TotalPrivateDaysFromRtoA()
 
  Dim cnn As Object
  Dim CRecordset3 As Object   
  
   ' Create a connection object
  Set cnn = CreateObject("ADODB.Connection")
 
   ' Connect to the database
  cnn.Provider = "SQLOLEDB"
  cnn.Open "Server=vaio;Database=Product;User Id=admin;Password=asd345po"
  If cnn.State <> 1 Then
     MsgBox "Could not connect to the database"
     Exit Sub
  End If
 
   ' Open recordset BeginNextRecordset
 
  Set CRecordset3 = CreateObject("ADODB.Recordset")
  CRecordset3.Open "EXEC sp_TotalPriceperMoth", cnn, adOpenDynamic, adLockPessimistic
 
 Sheets("Sheet1").[B4].CopyFromRecordset CRecordset3
   
 
    
  cnn.Close
  Set cnn = Nothing
 
End Sub

Open in new window

RomacaliAsked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Add this to the beginning of your stored procedure:

SET NOCOUNT ON

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Try this:

Public Sub TotalPrivateDaysFromRtoA()
 
  Dim cnn As Object
  Dim CRecordset3 As Object  
 
   ' Create a connection object
  Set cnn = CreateObject("ADODB.Connection")
 
   ' Connect to the database
  cnn.Open "Provider=SQLOLEDB;Server=vaio;Database=Product;User Id=admin;Password=asd345po"
  If cnn.State <> 1 Then
     MsgBox "Could not connect to the database"
     Exit Sub
  End If
 
   ' Open recordset BeginNextRecordset
 
  Set CRecordset3 = CreateObject("ADODB.Recordset")
  CRecordset3.Open "EXEC sp_TotalPriceperMoth", cnn, adOpenDynamic, adLockPessimistic
 
 Sheets("Sheet1").[B4].CopyFromRecordset CRecordset3
   
 
   
  cnn.Close
  Set cnn = Nothing
 
End Sub

On what line does the error occur?

Kevin
0
 
RomacaliAuthor Commented:
Sheets("Sheet1").[B4].CopyFromRecordset CRecordset3
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
zorvek (Kevin Jones)ConsultantCommented:
Try this line (perhaps the stored procedure name is misspelled):

  CRecordset3.Open "EXEC sp_TotalPriceperMonth", cnn, adOpenDynamic, adLockPessimistic

Kevin
0
 
RomacaliAuthor Commented:
hi Kevin,

it is correct, but still gives me the same error: "3704" Operation is not allowed when object is closed
pointing to this line   Sheets("ReferraltoAdmission").[B17].CopyFromRecordset CRecordset3

thanks,
Caliu
0
 
zorvek (Kevin Jones)ConsultantCommented:
What happens when you execute "EXEC sp_TotalPriceperMonth" in SQL Server Management Studio?

Kevin
0
 
RomacaliAuthor Commented:
It gives me these table:

On my procedure I have a
Drop table #tbl_Per_Month  but I tried to comment out and it didn't work :(


March      3.400
April      11.385
May      4.429
June      2.375
July      4.545
August      2.300
September      10.375
October      3.500
November      3.333
December      NULL
0
 
zorvek (Kevin Jones)ConsultantCommented:
Try this:

   CRecordset3.Open "EXEC sp_TotalPriceperMonth", cnn, adOpenForwardOnly, adLockReadOnly

Kevin
0
 
RomacaliAuthor Commented:
I tried still the same. I was wondering if the recorset returns all the table values... not sure why it is not working
0
 
zorvek (Kevin Jones)ConsultantCommented:
Run this version and tell me if anything is displayed in message boxes:

Public Sub TotalPrivateDaysFromRtoA()
 
  Dim cnn As Object
  Dim CRecordset3 As Object
  Dim oError As Object
 
   ' Create a connection object
  Set cnn = CreateObject("ADODB.Connection")
 
   ' Connect to the database
  cnn.Open "Provider=SQLOLEDB;Server=vaio;Database=Product;User Id=admin;Password=asd345po"
  If cnn.State <> 1 Then
     MsgBox "Could not connect to the database"
     Exit Sub
  End If
 
   ' Open recordset BeginNextRecordset
 
  Set CRecordset3 = CreateObject("ADODB.Recordset")
   CRecordset3.Open "EXEC sp_TotalPriceperMonth", cnn, adOpenForwardOnly, adLockReadOnly
 
 
    If cnn.Errors.Count > 0 Then
        For Each oError In cnn.Errors
            MsgBox IIf(oError.Number > 0, "Error #" & CStr(oError.Number) & Space(1), vbNullString) & oError.Description
        Next oError
    End If

 
 Sheets("Sheet1").[B4].CopyFromRecordset CRecordset3
   
 
   
  cnn.Close
  Set cnn = Nothing
 
End Sub

Kevin
0
 
RomacaliAuthor Commented:
the same error: Run_time error 3704 Operation is not allowed when the object is closed
pointing to:   Sheets("Sheet1").[B4].CopyFromRecordset CRecordset3

0
 
zorvek (Kevin Jones)ConsultantCommented:
Any message boxes displayed?

Kevin
0
 
RomacaliAuthor Commented:
not at all. :(
0
 
RomacaliAuthor Commented:
I'm attaching the procedure that it is calling.
Total.txt
0
 
RomacaliAuthor Commented:
you are GREAT! Thanks so much it worked:)
0
 
RomacaliAuthor Commented:
Kevin you are great!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.