Solved

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

Posted on 2009-04-10
16
226 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
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

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

777 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