title: Faster way to populate Excel with SQL data
body: <p>Using Excel 2010 and SQLServer with ActiveX DataObjects 2.8</p>
  
  <p>I'm retrieving about 100 records (variable no of rows each time) from a SQL database to an excel sheet. The query itself runs quickly (I see the status bar change when it returns), but it's taking a long time to populates the excel sheet--even with ApplicationUpdating off. </p>
  
  <p>The way I'm doing is is to loop over each row in the returned recordset and set the value of individual cells.</p>
  
  <p>Is there a faster way than iterating through each row and field in the recordset.</p>
  
  <p>Below is the snippet of the VBA:</p>
  
  <pre><code>Application.StatusBar = "Getting shipment data..."
  statement = "exec [SalesTax].[dbo].[GetShipmentsSummary]  " &amp; _
  "@JobNo = '" &amp; JobNo &amp; "'"
  
   Set rs = conn.Execute(statement)
      If (rs.BOF And rs.EOF) Then
          MsgBox ("No shippment records were found for JobNo: " &amp; JobNo)
          Exit Sub
      End If
      rs.MoveFirst
  
      Application.StatusBar = "Got shipment data. Processing..."
      Application.ScreenUpdating = False
      ' Get each row and set cells to appropriate fields
      Do While Not rs.EOF
           ' Now insert the shipment data for this row
              Set CurrRange = Sheets(WSName).Cells(CurrRowNo, CurrColNo)
               CurrRange.Value = rs("State")
              Set CurrRange = Sheets(WSName).Cells(CurrRowNo, CurrColNo + 1)
               CurrRange.Value = CStr(rs("FirstZIP")) 
              Set CurrRange = Sheets(WSName).Cells(CurrRowNo, CurrColNo + 2)
               CurrRange.Value = CStr(rs("StateCount"))
              Set CurrRange = Sheets(WSName).Cells(CurrRowNo, CurrColNo + 3)
               CurrRange.Value = rs("StatePercentage")                
          CurrRowNo = CurrRowNo + 1
      rs.MoveNext
      Loop
  
   conn.Close
  Set conn = Nothing
  Set cmd = Nothing
  Application.ScreenUpdating = True
  Application.StatusBar = "Ready"
  </code></pre>
  
  <p>Exit Sub</p>
  
  <p>Thanks Mark</p>