Export a recordset to an Excel

[PotatoTang]

Hi,

I would like to ask :
Other by using a looping to export record one by one to an Excel file, is there any quick method which can export whole recordset to Excel at once?

Thanks

 

[Schweiger]

If yoou don't really mind looping through a recordset but are just looking to speed up things because it takes so much time to transfer data to excel if you do it cell for cell you can build a tab-separated string of the recordset and transfer the whole string using the clipboard.

Dim strBuffer As String
Dim fld As Field
Dim objExcel As Excel.Application
    
' Copy Recordset to string buffer
rs.MoveFirst
Do While Not rs.EOF
    For Each fld In rs.Fields
        strBuffer = strBuffer & fld.Value & vbTab
    Next fld
    If strBuffer <> &quot;&quot; Then
        strBuffer = Left(strBuffer, Len(strBuffer) - 1)
    End If
    strBuffer = strBuffer & vbCrLf
    rs.MoveNext
Loop
' Copy Stringbuffer to clipboard
Clipboard.Clear
Clipboard.SetText strBuffer
' Insert clipboard in excel
Set objExcel = New Excel.Application
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.ActiveSheet.Paste

hope this helps
Andreas

 

[CCLINT]

It depends where the data is coming from. If it comes from a database table such as a JET Mdb, you can use ADO and the JET provider to do a bulk export of the data to the Excel table. [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[PotatoTang]

yes, thanks Andreas, I would try it


Yes, I am using ADO to get the recordset from SQL Server. CCLINT, how to do a bulk export to an Excel file?

thanks a lot!