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

[modpluz]

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

[RobDog888]

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

[modpluz]

sorry i forgot to include that.

VB6 using ADODB

excel and access 2000

[RobDog888]

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

[modpluz]

thanks robdog

[RobDog888]

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

[modpluz]

i don't mind a little more help

[RobDog888]

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.

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

[modpluz]

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.