Importing data from Excel into Access[Resolved]

[engineer]

Dear All,


I have an Excel sheet. It contains two coulums of data(for example employee# and name).

I have to import data from this excel sheet into Access table.

How can I do this.

Plz Guide.

[RhinoBull]

Using VB you can automate MS Access so you can import data directly into it - set references to MS Access x.x and run this sample code:

Private Sub ImportExcelSheet(strMdbPath As String, strExcelPath As String, strSheetName As String)
'==================================================================================================
Dim AccessDB As Access.Application
 
    Screen.MousePointer = vbHourglass
    Set AccessDB = New Access.Application
    AccessDB.OpenCurrentDatabase strMdbPath
    AccessDB.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                                       strSheetName, strExcelPath
    AccessDB.DoCmd.TransferDatabase acExport
    AccessDB.Quit acQuitSaveNone
    Set AccessDB = Nothing
    Screen.MousePointer = vbDefault
 
End Sub
 
'usage:
Private Sub Command1_Click()
    
    ImportExcelSheet App.Path & "\nwind.mdb", _
                     App.Path & "\receipts.xls", _
                     "Receipts"
 
End Sub

[engineer]

Thanks RhinoBull.

I am gona try this.

[RobDog888]

Actually if you just open the Access db and either run the Transfer Spreadsheet menu command or the DoCmd.TransferSpreadsheet in a module you dont need to complicate the issue unless you need it like RB posted, to execute from a VB program.

Moved from Classic VB forum.

[engineer]

Thanks for the great help.