[FAQ's: OD] How do I transfer data between Excel and Access?

[RobDog888]

You can perform the transfer many ways. Using Access VBA, ActiveX Data Objects and Excel's Object Model, etc. I'm not going to cover using other databases in this thread, just Access. For MS SQL Server, see this FAQ Thread or this FAQ Thread.

You can do the transfer from the Excel point of view or also from the Access point of view. See this FAQ Thread for the Access examples.


Excel 2003 VBA Code Example:

Option Explicit
'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 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;User Id=admin;Password=;"
    oCnn.Open
    'Create your recordset
    Set oRs = New ADODB.Recordset
    oRs.Open "SELECT * FROM Table1;", oCnn, adOpenKeyset, adLockReadOnly, adCmdText
    'Add to your current workbook and add the field names as column headers (optional)
    For i = 0 To oRs.Fields.Count - 1
        Workbooks("Book1").Sheets(1).Cells(1, i + 1).Value = oRs.Fields(i).Name
    Next
    Workbooks("Book1").Sheets(1).Range("1:1").Font.Bold = True
    Workbooks("Book1").Sheets(1).Cells(2, 1).CopyFromRecordset oRs
    'Clean up ADO Objects
    oRs.Close
    Set oRs = Nothing
    oCnn.Close
    Set oCnn = Nothing
End Sub

[RobDog888]

Here is how you export your Access db table.
Populate an ADO recordset, create a new Excel workbook, and then copy the entire recordset into a sheet, ex. sheet1.

VB 6 And ADO Code Example:

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;User Id=admin;Password=;"
    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
    'Clean up ADO Objects
    oRs.Close
    Set oRs = Nothing
    oCnn.Close
    Set oCnn = Nothing
    'Clean up Excel Objects
    oWB.Close SaveChanges:=True, FileName:="D:\Test.xls"
    Set oWB = Nothing
    oApp.Quit
    Set oApp = Nothing
End Sub