exporting ADO recordset straight into CSV or Excel?

  • Dear all,

    I would like to export the content of a recordset (usually a stored procedure with various parameters) into a CSV or Excel file directly without showing it in a form first.

    Is it possible to export directly a recordset to a CSV / Excel file?

    I saw the concept of Stream in the ADO documentation. Apparently, A record can be a source for a Stream. Can a recordset be used too?

    Cheers

    Eric

  • In Access you can do a DoCmd.TransferSpreadsheet (Excel) or DoCmd.TransferText (tab or csv) using a query as the datasource

  • Thanks

    I saw a piece of VBA code starting excel and sending an ADO recordset to it.

    It does the job for me perfectly because I can create the recordset the way I want, for instance, with a stored procedure and parameters

    Cheers

    Eric

  • How about posting your code so the next person can gain from your experience?

    "When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
    "Robert A. Heinlein"

  • also don't forget to write a schema.ini file in same folder where the source or result table is written. here you specify for example the delimiter or the column names and widths

  • Good idea, I did not think about this...

    Here is the vba code

    I initially had a reference to the Excel Libraries because it makes the code "cleaner" by using early binding to these objects but it fell down when it turned out my client has an older version of Excel.

    I had to go back to late binding and "object" rather than "Excel.Application", etc

    I also did not do anything in regards to column definitions because I have no strong requirement for it and the less I "touch" a customer machine, the better.

    For the record, I would have preferred to use a Web interface to all this but that was not possible for "political/commercial" reasons.

    Finally, what I find really useful in this approach is that there is no precondition on how the recordset is defined, so I can use a stored procedure with named parameters if i wish (due to ADO dodgy parameter handling, I tend to fire all this as a Text command rather than stored procedure)

    Public Sub ExportToExcel(ByRef rst As ADODB.Recordset, filename As String)

    On Error GoTo Err:

    ' I could not use early binding in my case because my client has an older version of Excel

    ' but it may not be a problem for you

    ' Dim createExcel As New Excel.Application

    ' Dim Wbook As Excel.Workbook

    ' Dim Wsheet As Excel.Worksheet

    ' Set Wbook = createExcel.Workbooks.Add

    ' Set Wsheet = Wbook.Worksheets.Add

    Dim createExcel As Object

    Set createExcel = CreateObject("Excel.Application")

    Dim Wbook As Object

    Set Wbook = createExcel.Workbooks.Add

    Dim Wsheet As Object

    Set Wsheet = Wbook.Worksheets.Add

    Dim fieldIdx As Integer

    ' writing column headers

    For fieldIdx = 0 To rst.Fields.Count - 1

    Wsheet.Cells(1, fieldIdx + 1).Value = rst.Fields(fieldIdx).Name

    Next fieldIdx

    '' looping through rows and writing in spreadsheet

    Dim rowIdx As Integer

    If (rst.RecordCount > 0) Then

    rst.MoveFirst

    For rowIdx = 0 To rst.RecordCount - 1

    For fieldIdx = 0 To rst.Fields.Count - 1

    Wsheet.Cells(rowIdx + 2, fieldIdx + 1).Value = rst(fieldIdx).Value

    Next fieldIdx

    rst.MoveNext

    Next rowIdx

    End If

    Wbook.SaveAs filename

    Wbook.Close True

    Set Wbook = Nothing

    Set Wsheet = Nothing

    Set Wbook = createExcel.Workbooks.Open(filename)

    createExcel.Visible = True

    Set createExcel = Nothing

    Exit Sub

    Err:

    Select Case Err.Number

    Case 32755

    MsgBox "Press Cancel button"

    Case 1004

    MsgBox "Cannot save file '" & filename & "' (is it open?)"

    Wbook.Close False

    Case Else

    MsgBox Err.Number & " " & Err.Description

    End Select

    Set createExcel = Nothing

    Set Wbook = Nothing

    Set Wsheet = Nothing

    End Sub

  • Like what you did.

    Thanks for the follow up.

    I know people will be helped from your issue and solution. 🙂

    "When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
    "Robert A. Heinlein"

  • You can simplify this by using Excel's CopyFromRecordset method:

    Replace this:

    Dim rowIdx As Integer

    If (rst.RecordCount > 0) Then

    rst.MoveFirst

    For rowIdx = 0 To rst.RecordCount - 1

    For fieldIdx = 0 To rst.Fields.Count - 1

    Wsheet.Cells(rowIdx + 2, fieldIdx + 1).Value = rst(fieldIdx).Value

    Next fieldIdx

    rst.MoveNext

    Next rowIdx

    End If

    With this:

    ' not looping through rows, but copying the recordset in one command

    if not rst.Eof Then Wsheet.Range("A2").CopyFromRecordset rst

  • Nice!

    Thank You 😉

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply