Excel VBA Macro to Execute a Stored Procedure
The other day I came across a problem of running an MS SQL 2005 stored procedure in an Excel VBA macro. I had set this up before with another stored proc, which worked just fine. Copying this code (and later trying several other ways of doing it) always ended up with a closed recordset being returned. My exact error was a run-time error 3704 with the message “Operation is not allowed when the object is closed.” The code looked something like this:
‘ Create new recordset object
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
‘ Get data to populate spreadsheet
‘ Assign the Connection object.
rs.ActiveConnection = cn
rs.Open “exec database.dbo.storedProcName”, , adOpenDynamic, adLockPessimistic
‘ The error came up here
Application.Workbooks(“Spreadsheet.xls”).Worksheets(“InvAdj”).Range(“A2”).CopyFromRecordset rs
‘ Clean up
rs.Close
Set rs = Nothing
There didn’t seem to be anything wrong with the proc, which ran fine in the management studio, and the VBA code was identical to what I had used in another macro to do the exact same thing (just return data). A lot of Googling didn’t turn up much, but it sort of led me to believe it had something to do with the temp tables used in it. Going off of this, I finally found my problem. The proc was returning multiple recordsets, and the first one I get back after the .Open was empty. I needed to add the following after the open:
Set rs = rs.NextRecordset()
Set rs = rs.NextRecordset()
Set rs = rs.NextRecordset()
Although ugly, this got me through the other recordsets I got back and let me get to the data I needed. My hope is that I can get this p
ost crawled enough to show up sooner in some other poor soul’s searches for this same problem.