Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2009-04-10
16
Medium Priority
?
235 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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
 
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 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

604 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