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

[RobDog888]

VB 6 Import Code Example

Option Explicit
'Add a reference to Microsoft Access xx.0 Object Library
Private moApp As Access.Application
 
Private Sub Command1_Click()
    On Error GoTo My_Error
 
    Set moApp = CreateObject("Access.Application")
    moApp.DoCmd.SetWarnings False
    moApp.OpenCurrentDatabase "C:\RobDog888.mdb"
    moApp.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Excel_Sheet2", "C:\Book1.xls", False, "Sheet2$"
    moApp.Visible = True
    moApp.DoCmd.OpenTable "Excel_Sheet2", acViewNormal, acEdit
    moApp.DoCmd.SetWarnings True
    Exit Sub
My_Error:
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbExclamation
End Sub
 
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    On Error GoTo My_Error
    
    moApp.CurrentDb.Close
    moApp.Quit acQuitPrompt
MyError:
    Set moApp = Nothing
End Sub