CopyFromRecordSet In Excel 2003

I want to use the CopyFromRecordSet however I am using a stored procedure that returns a recordset. Here is the code, but the recordset never opens. I have commented out the Execute

Here is a code snip, any Ideas? TIA Mike

Dim Con1 As New ADODB.Connection
Dim Cmd1 As New ADODB.Command
Dim Rs As New ADODB.Recordset

Con1.Open

Cmd1.ActiveConnection = Con1
Cmd1.CommandType = adCmdText
Cmd1.CommandText = "MyStroredProc"
Cmd1.Execute
Rs.Open Cmd1

Worksheets("T1").Range("B1").CopyFromRecordset Rs

I believe it should be like

Con1.Open

Set Cmd1.ActiveConnection = Con1
Cmd1.CommandType = adCmdStoredProc ' Please verify spelling for adCmdStoredProc, since I do not have documentation with me
Cmd1.CommandText = "MyStroredProc"
Set Rs = Cmd1.Execute

|||

VMazur,

Thank you for the code snip however when I query the recordset state, it is still closed or 0 and I generate an error. I did use 4 as the commandType

Any other thoughts would be appreciated.

'Open the connection
Con1.Open

Set Cmd1.ActiveConnection = Con1
Cmd1.CommandType = 4 '' Stored Proc
Cmd1.CommandText = "REPORT_procASDCStatus"
Set Rs = Cmd1.Execute

'Rs.Open Cmd1 '' Tried this also
Worksheets("T1").Range("A1").CopyFromRecordset Rs <<Fails with message that rs is not open

MikeD

|||What happens when you call Execute method? Does it generate any error or not?|||

The issue was Set NO COUNT ON in the SP. This was missing in the SP.

As soon as I added this, the recordset opened and I could do copy from.

Thank you Mazur

|||

Hi Mazur,

There was no error message, only issue was there was no data transfer. As soon as we set NOCOuNT ON, data started transfering.

This question is resolved.

Thank you

Mike