Solved

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

Posted on 2009-04-10
16
225 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:Romacali
  • 9
  • 7
16 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 24118310
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
 

Author Comment

by:Romacali
ID: 24118753
Sheets("Sheet1").[B4].CopyFromRecordset CRecordset3
0
 
LVL 81

Expert Comment

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

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

Kevin
0
 

Author Comment

by:Romacali
ID: 24119114
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 24119564
What happens when you execute "EXEC sp_TotalPriceperMonth" in SQL Server Management Studio?

Kevin
0
 

Author Comment

by:Romacali
ID: 24119823
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 24120024
Try this:

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

Kevin
0
 

Author Comment

by:Romacali
ID: 24120047
I tried still the same. I was wondering if the recorset returns all the table values... not sure why it is not working
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 24120080
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
 

Author Comment

by:Romacali
ID: 24120136
the same error: Run_time error 3704 Operation is not allowed when the object is closed
pointing to:   Sheets("Sheet1").[B4].CopyFromRecordset CRecordset3

0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 24120241
Any message boxes displayed?

Kevin
0
 

Author Comment

by:Romacali
ID: 24120242
not at all. :(
0
 

Author Comment

by:Romacali
ID: 24120257
I'm attaching the procedure that it is calling.
Total.txt
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 24120268
Add this to the beginning of your stored procedure:

SET NOCOUNT ON

Kevin
0
 

Author Comment

by:Romacali
ID: 24120274
you are GREAT! Thanks so much it worked:)
0
 

Author Closing Comment

by:Romacali
ID: 31569006
Kevin you are great!
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

919 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

20 Experts available now in Live!

Get 1:1 Help Now