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
Solved

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

Posted on 2009-04-10
16
227 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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…

792 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