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

[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