Using Excel 2010 and SQLServer with ActiveX DataObjects 2.8
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.
The way I'm doing is is to loop over each row in the returned recordset and set the value of individual cells.
Is there a faster way than iterating through each row and field in the recordset.
Below is the snippet of the VBA:
Application.StatusBar = "Getting shipment data..."
statement = "exec [SalesTax].[dbo].[GetShipmentsSummary] " & _
"@JobNo = '" & JobNo & "'"
Set rs = conn.Execute(statement)
If (rs.BOF And rs.EOF) Then
MsgBox ("No shippment records were found for JobNo: " & 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"
Exit Sub
Thanks Mark