I am trying to populate an Excel spreadsheet with data retrieved from SQL via ADO but I am getting an error is:

Run-time error "3704':
Operation is not allowed when the object is closed

The code so far is:

Const SHEET_NAME As String = "Data"
Const SQL_SERVER As String = "SQLServer1"
Const SQL_DATABASE As String = "myDatabase"
Const STORED_PROCEDURE As String = "spMeasurements"

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim param As ADODB.Parameter

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;USER ID=**; Password=**** ;Persist Security Info=True;Use Procedure for Prepare=1;Auto Translate=True;"
cn.Properties("Data Source") = SQL_SERVER
cn.Properties("Initial Catalog") = SQL_DATABASE
cn.Open

Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = STORED_PROCEDURE
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 130
Set param = cmd.CreateParameter("@WG", adVarChar, adParamInput, 10, WG)
cmd.Parameters.Append param
Set rst = cmd.Execute()

Since I have opened the connection (see code with yellow background) I don't know what the error message is telling me. rst.Fields.Count = 0
Can someone explain and suggest a fix?

Thanks!

hello,

On what line does the error happen? Is it the cmd.Execute line?

The initial thing I see that might be causing the problem might be your connection string. The Persist Security Info=True;Use Procedure for Prepare=1;Auto Translate=True; options are unfamilar, I tend to use the following.

"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=myUsername;Password=myPassword;"

I discovered the cause of my problem and have fixed it:

The code I used was copied from another application and as I was unfamiliar with the latter two connection string clauses (Use Procedure for Prepare=1;Auto Translate=True;) I removed them but the same error came up. I tried using a different, random, stored procedure to see if the problem was with my sp (even though it works perfectly in query analyzer) and I found that, indeed, the other sp did not give me the error and brought back a good result set. So I examined my sp again and realized that it contained some Print statements I had used for QA purposes. I commented them out and - voila! - it worked.  

Derek - thanks for being willing to help.

To answer your question, though, no the error didn't come up on the cmd.execute line. Execution went on its merry way until I tried to do a CopyFromRecordset, then it errored.

I have the exact problem with the exact error message.

Here is my excel macro code:

Sub Test()
Dim RangeA As Range
Dim RangeB As Range

Set RangeA = Worksheets("CS").Range("B3")
Set RangeB = Worksheets("CS").Range("G3")

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim stConn As String
Dim i As Integer, j As Integer

stConn = "Server=MyPc;Database=XXXX;uid=xxxx;pwd=xxxx"
With conn
.Provider = "SQLOLEDB"
.ConnectionString = stConn
.Open
If conn.State = ADODB.adStateOpen Then
Set rst = conn.Execute(proc_Test @Start='" & CDate(RangeA.Value) & _
"'" & ", " & "@End='" & CDate(RangeB.Value) & "'")
j = 5
While Not (rst.BOF Or rst.EOF)
For i = 0 To rst.Fields.Count - 1
ActiveSheet.Cells(j, i + 1).Value = rst.Fields(i).Value
Next
j = j + 1
rst.MoveNext
Wend
rst.Close
conn.Close
End If
End With

Set conn = nothing
Set rst = nothing

End Sub

The yellow highlight is where it crashed with that error message. I ran that sproc in QA and it worked. My sproc had two select statement. The 1st select queries against multiple tables using inner join and few aggregate functions. Then the result is "fetch" into a temporary table (@Tb) created inside the sproc. This is local temp table. Last 2nd select queries against this temp table. I have even tried to use global temp table (##Tb) yet it still crashed.

I knew the exec statement in my macro did run successfully because I ran a simple select statement against the global temp table when I stepped thru my code. That's how I find out the status.

I have tried to use "Trusted Connection = yes" and "Integrated Security = SSPI" for my connection, it makes no diff. Can anyone shed some light on how I can fix this? Thank you in advance.