title: Excel VBA Run-time Error '3704'
body: <p>I am getting run-time error '3704' when trying to dump a recordset into the tab of an Excel 2010 workbook. The recordset should contain a couple of hundred records that come from a stored procedure I wrote using SQL Server 2008 R2. I know the exact same statement executes when ran in SQL Server Management Studio and I have used the same connection string in the past so I am pretty sure those parts of my code are working correctly.</p>
  <p>I have researched the error and the only solutions I have seen have to do with the connection timing out. You will see that I have set CommandTimeout = 30 (I assume that is in seconds). I get the error in just a few seconds of clock time so I am sure that this is not a problem with my connection timing out.</p>
  <p>My VBA code:</p>
  <pre><code>Sub Add_Results_Of_ADO_Recordset()
       
      Dim cnt As ADODB.Connection
      Dim rst As ADODB.Recordset
      Dim stSQL As String
      Dim wbBook As Workbook
      Dim wsSheet As Worksheet
      Dim rnStart As Range
       
       
      Const stADO As String = &quot;Provider=SQLOLEDB.1;User ID =xxxxx;Password=xxxxx;&quot; &amp; _
      &quot;Persist Security Info=False;&quot; &amp; _
      &quot;Initial Catalog=MyDatabase;&quot; &amp; _
      &quot;Data Source=xxxxxxxxxxxx&quot;
      
      Set wbBook = ActiveWorkbook
      Set wsSheet = wbBook.Worksheets(1)
       
      With wsSheet
          Set rnStart = .Range(&quot;TopLeft&quot;).Offset(1, 0)
      End With
       
      Set cnt = New ADODB.Connection
       
      With cnt
          .CursorLocation = adUseClient
          .Open stADO
          .CommandTimeout = 30
      End With
          
      stSQL = &quot;EXEC MyDatabase.dbo.PolicyList '2/1/2014','2/1/2014','BOOK'&quot;
      
      
      With cnt
       Set rst = .Execute(stSQL)
      End With
      
      'Dump recordset into my WorkBook
      rnStart.CopyFromRecordset rst    'This is where the error occurs!!!
          
       
       'Cleaning up.
      rst.Close
      cnt.Close
      Set rst = Nothing
      Set cnt = Nothing
       
  End Sub
  </code></pre>
  <p>This is the error message I get:</p>
  <blockquote>
  <p>Run-time error '3704':</p>
  <p>Operation is not allowed when object is closed.</p>
  </blockquote>