Faster way to populate Excel with SQL data

Question

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

Answer 1

Rather than loop through each record you could assign the full recordset to an array using

arrData = rs.GetRows

However, you may need to transpose this array to be able to output to an Excel range.

Another alternative if you just need it in a sheet range is using

outputRng.CopyFromRecordset rs

Where outputRng is a range object.

Ps - It should be Application.Statusbar = False to reset the status bar.

Answer 2

Have you tried turning off Automatic Calcuation?

Turn off both ScreenUpdating and Calculation

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Turn it back on after you finished populating the table.

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Hope this helps.

Answer 3

The fastest way to write a spreadsheet is to write a csv file directly -- it's just a text file, and it is pretty easy -- you don't get formatting, formulas, etc. (though you don't appear to use them)

However, you should never use the Microsoft ActiveX controls from any server. It is not supported cf. Limitations of Office Web Components when used server-side, and you will be virtually certain to have problems if you put this in production.

There are a both commercial and free libraries that do not depend on ms ActiveX, that make this task straightforward on a server or workstation. StackOverflow seems to frown on library and framework recommendations though.

ADDED

Most of the 3rd party libraries will be considerably faster than using Office Automation too.

ADDED

I linked the wrong article. Should have been Considerations for server-side Automation of Office Do not use office automation components on a server. I have personally been asked to fix this several times by companies that did this. It can be working fine for a while then crash completely, but if you have an active server it will likely run into problems very quickly.