I have been trying for days now to open a recordet in excel 2007 from a stored procedure in SQL Server. The error I receive is "operation not allow on closed object." So I put some messagin in my code to determine if the connection or the recordset was the problem. The connection state is open, however the recordset state is closed. When I execute the code, it actually pauses on the open recordset as if it is retreiving the data, but the next line of code tests its state and it is always closed. I validated permissions on the SP, and that should not be a problem. Please look at this code and help me figure out what could possibly be wrong or what I need to test. Thanks!
Dim strPart As String
Dim cn As ADODB.Connection
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim strConn As String
Dim qt As QueryTable
Set cn = New ADODB.Connection
strPart = Worksheets("M2MBOM").Range("B1")
strConn = "PROVIDER=SQLOLEDB; DATA SOURCE=MyServer; INITIAL CATALOG=Database01; USER ID=test; Password=test"
'This procedure requires one parameter (i also tested with another proc requiring no variables, does not work
strSQL = "Proc_Get_Item strPart"
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cn
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly
'Here the connection state is 1
'Here the rs state is 0
If rs.State = adStateOpen Then
If rs.BOF And rs.EOF Then
MsgBox "This Part Number does not have a Bill of Materials."
Set rs = Nothing
MsgBox "Unable to open recordset. Please notify adminsitrator."
Set cn = Nothing