[RESOLVED] export from ms access .mdb to excel .csv

how do you export the ms access recordset data into an excel .csv?


and one more thing how do you create a .csv/.mdb file at runtime

Are you connecting to your db from VB6 using ADO? What versions of Access and Excel are you using?

sorry i forgot to include that.

VB6 using ADODB

excel and access 2000

Ok, then if you have a ADO recordset that is the source for exporting to Excel then you can just use the .CopyFromRecordset method.

thanks robdog

If you need anymore help with Excels .CopyFromRecordset method let me know. I can write a small example if you need it. :)

i don't mind a little more help

Ok, here is how you connect to your Access db, populate a recordset, create a new Excel workbook, and copy the entire recordset into sheet1.

VB Code:

Option Explicit
'Add a reference to MS Excel xx.0 Object Library
'Add a reference to MS ActiveX Data Objects 2.x Library
Private Sub Command1_Click()
    Dim oRs As ADODB.Recordset
    Dim oCnn As ADODB.Connection
    Dim oApp As Excel.Application
    Dim oWB As Excel.Workbook
    Dim i As Integer
    'Connect to your Access db
    Set oCnn = New ADODB.Connection
    oCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\RobDog888.mdb;Persist Security Info=False"
    oCnn.Open
    'Create your recordset
    Set oRs = New ADODB.Recordset
    oRs.Open "SELECT * FROM Table1;", oCnn, adOpenKeyset, adLockReadOnly, adCmdText
    'Create an instance of Excel and add a new blank workbook
    Set oApp = New Excel.Application
    oApp.Visible = False
    Set oWB = oApp.Workbooks.Add
    'Add the field names as column headers (optional)
    For i = 0 To oRs.Fields.Count - 1
        oWB.Sheets(1).Cells(1, i + 1).Value = oRs.Fields(i).Name
    Next
    oWB.Sheets(1).Range("1:1").Font.Bold = True
    oWB.Sheets(1).Cells(2, 1).CopyFromRecordset oRs
    oRs.Close
    Set oRs = Nothing
    oCnn.Close
    Set oCnn = Nothing
    oWB.Close SaveChanges:=True, FileName:="D:\Test.xls"
    Set oWB = Nothing
    oApp.Quit
    Set oApp = Nothing
End Sub

found something, after creating the .CSV it opens it and also open a new .XLS(named Book1) file containing all tables and field defined in the SQL statement.

why does that happens and how can i stop it.