Fastest way to splat data into excel? 1

[jsteph]

Ok, I have a simple data table in sql server. In vba I'm selecting a subset of records, and I want those to go into an excel sheet.

I'm currently doing something like

...<dim and set all excel.app objects, open file,open rs etc>
Do Until rs.eof
    lCount = lCount + 1
    for i = 1 to rs.fields.count '(skipping first field; <26 fields)
        objExcel.Range(chr(64 + i) & lCount) = rs(i).value
    next i
    rs.movenext
loop

This works, but is very slow--like 10 seconds for 500 20-field records. Since this is a relatively simple set of data, isn't there a way to write the recordset to one big string variable, maybe with delimiters and line-breaks, and then copy that string to clipboard and paste to the excel.worksheet? Or some way, any way, to just overlay the recordset data right into excel without having to do it cell-by-cell?

I've thought about excel's import/export routines, or an odbc-link, but it seems like the overhead of that might be high. One of the things to consider here is that the recordset is already open for another part of the process, so that time is a freebie--the data is there already and just needs to splat into Excel. So any odbc-links or imex routines from the excel object would likely have to create their own recordset which would add cost.

Maybe the .Range method is the wrong choice? What would be faster?
Thanks for any guidance,
--Jim

 

[macropod]

Hi Jim,

If your sql server can output the data to a comma-delimited file that can be saved with a csv extension, Excel could open it directly. This is much faster than using vba. From there a simple copy/paste routine would do the trick.

Cheers

 

[fredericofonseca]

use the copyfromrecordset method of the range object.

Also if you don't need the first field then just ommit it on the select statement.

If you can't ommit it then you have two (of more) options.

1- Copy all the fields into the destination, and then issue a cut and past of only the cells you need.

e.g. your select has 5 fields, and 10 rows, and you copy it into cell B10.

to copy
Range("B10:E20").Select
Range("E20").Activate
Selection.Cut
Range("A10").Select
ActiveSheet.Paste
The above is the result of recording a macro. Code needs to be changed to catter for dynamic ranges.

2- create an empty worksheet, and use the copyfromrecordset to populate that spreadsheet. Then use copy+paste into the real spreadsheet.

All the above can be done with code.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SkipVought]

I agree with Frederico -- best to use the CopyFromRecordset.

But here's one for future reference regarding your code. Each time you evaluate the Chr function, it takes a fraction of time that adds up. ALSO eachtime your code evaluates an OBJECT, each [DOT] node takes a fraction of time to evaluate. This does the same things, albeit more efficiently...

[b]With objExcel[/b]
  Do Until rs.eof
    lCount = lCount + 1
    for i = 1 to rs.fields.count '(skipping first field; <26 fields)
        [b].cells(lCount, i) = rs(i).value[/b]
    next i
    rs.movenext
  loop 
[b]End With[/b]

Furthermore, you could run a time trial to see if using With objExcel or With rs would add to performance.

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[jsteph]

frederico,
Thanks very much--that took the 10 second time down to about 3 seconds, which is significant. I did have to re-open a separate recordset, since the copyfromrecordset requires an ODBC/DAO recordset and the rs I had open was ADO, but the time was still faster.

This is all part of a vb .dll, so it would be nice if Excel would update that method to use ado, since the dao requires the project to have that DAO reference and include the odbc dsn on the machine, and all the compatibility issues there, but this works beautifully regardless.

skip,
I implemented that With syntax, I had never used it before because I always thought its was more for neat code than performance. I didn't do a time trial on that because I'd implemented that at the same time as the other changes so there wasn't a separate run where just that was changed, but I'm sure that helps performance at least a little.
Thanks very much,
--Jim

 

[fredericofonseca]

Something wrong there. copyfromrecordset allows ADO also.
Excel 2003 (2000 also works), with MDAC 2.6, 2.7 or 2.8

small sample I just created.
Sub load_file()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim constring As String
Dim sql As String
sql = "SELECT last_run FROM process_run_date"
constring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\vbcode\invoice.mdb;User Id=admin;Password="

Set conn = New ADODB.Connection
conn.Open constring
Set rs = New ADODB.Recordset
rs.Open sql, conn
Range("A1").CopyFromRecordset rs
End Sub

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[jsteph]

Frederico,
I tried the method with ADO and it didn't work in Excel 97, which is what I had, but I got the excel9.olb and now it works, so 2000 must've been the version where they made that method work with ado.

Anyway, it's working great--thanks again!
--Jim

 

[petroffp]

1. Repeatedly calling rs.fiekds.Count slows things down
2. Try loading results in an array and then popping the array into Excel cells.

Try this

Dim Results() as Variant
Dim FieldCount as Integer
Dim R as objExcel.Range
FieldCount = rs.fields.Count
ReDim Results(1 To FieldCount)

Do Until rs.eof
    lCount = lCount + 1
    for i = 1 to FieldCount '(skipping first field; <26 fields)
        Results(I) = rs(i).value
    next i
' pop Results array into Excel range.
    Set R = objExcel.Range(Cells(lcount, 1), Cells(lcount, fieldcount))
    R  = Results
    rs.movenext
loop

If you can reasonably determine the rs.count then you can dimension a 2xdimensional array and load the entire recordset into a two dimensional array, and then pop the entire recordset into excel outside of the loop.

Caveat: I haven't tested this recently, but remember this technique working for me some time ago. The Excel transfer from a VBA array to an Excel range is VERY fast.

BTW, the results can be popped into a vertical Excel range via.
Set R = objExcel.Range(Cells(1, lcount), Cells(fieldcount, lcount))
R = Application.Transpose(Results)

 

[PHV]

You may consider the GetRows method of the ADODB.Recordset object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jsteph]

Thank you all. Since I went with Excel 2000, I won't need the GetRows--MS recomends frederico's CopyFromRecordset Method over Getrows for >97. I assume internally the mechanics are very similar, however--maybe a layer of abstraction or two is removed.

As for the rs.count and chr() calls, I do typically change that sort of stuff as I get the larger 'proof of concept' stuff ironed out. And yes the .Cells(n,n) method Skip suggested was faster than the .Range("A"n) method--I'm doing time testing with GetTickCount and am adjusting the finer points.

Alas, at this point after the initial changes, all of my subsequent shaving of ticks is not going to make a noticable difference--this is all part of a .dll that a website uses to output an Excel sheet to our intranet users. So the network time alone to transfer the sheet is still somewhat greater than the time it takes to build the sheet, but the first few changes suggested here did take seconds away which was indeed noticable, and I do like to be in the habit of writing efficient code nevertheless.
Thanks again,
--Jim