April 29, 2010 at 8:26 am
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
May 11, 2010 at 1:51 am
In Access you can do a DoCmd.TransferSpreadsheet (Excel) or DoCmd.TransferText (tab or csv) using a query as the datasource
May 13, 2010 at 1:02 pm
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
May 14, 2010 at 10:42 am
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"
May 17, 2010 at 12:16 am
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
May 17, 2010 at 1:47 am
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
May 17, 2010 at 11:23 am
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"
May 18, 2010 at 2:03 am
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
May 18, 2010 at 2:11 am
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