import excel data into existing access table using vba

[wejdan1994]

Hello everyone,

I would like to ask you if you have example file to import (append) data from excel to existing access table via vba codes ?

Note: the code does not contain a specific Excel name.


thank you,

 

[theDBguy]

Hi. Have you tried the TransferSpreadsheet method?

 

[wejdan1994]

From external data tab ?
I need form that enable me to import data from any formatted excel to existing table in access

 

[theDBguy]

From external data tab ?
I need form that enable me to import data from any formatted excel to existing table in access

No, by using VBA. That's what you asked for, right? Have you tried it?

 

[wejdan1994]

Private Sub btnImportSpreadsheet_Click()
Dim FSO As New FileSystemObject

If Nz(Me.txtFileName, "") = "" Then
MsgBox "Please select a file!"
Exit Sub
End If

If FSO.FileExists(Nz(Me.txtFileName, "")) Then
ExcelImport.ImportExcelSpreadsheet Me.txtFileName, FSO.GetFileName(Me.txtFileName)
Else
MsgBox "File not found!"
End If

End Sub


I only tried this code, but this code used to import data from excel to new table not to specific table. otherwise no

 

[theDBguy]

Private Sub btnImportSpreadsheet_Click()
Dim FSO As New FileSystemObject

If Nz(Me.txtFileName, "") = "" Then
MsgBox "Please select a file!"
Exit Sub
End If

If FSO.FileExists(Nz(Me.txtFileName, "")) Then
ExcelImport.ImportExcelSpreadsheet Me.txtFileName, FSO.GetFileName(Me.txtFileName)
Else
MsgBox "File not found!"
End If

End Sub


I only tried this code, but this code used to import data from excel to new table not to specific table. otherwise no

I suggest you try it and let us know how it goes.

 

[Gasman]

TransferSpreadsheet

 

[wejdan1994]

Solved, appreciated.
I used the below Code:

Public Sub ImportExcelSpreadsheet(fileName As String, tableName As String)
On Error GoTo BadFormat
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "EmpDoc", fileName, True
Exit Sub

BadFormat:
MsgBox "The file you tried to import was not an Excel spreadsheet."
End Sub


Note: EmpDoc = access table name

 

[theDBguy]

Solved, appreciated.
I used the below Code:

Public Sub ImportExcelSpreadsheet(fileName As String, tableName As String)
On Error GoTo BadFormat
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "EmpDoc", fileName, True
Exit Sub

BadFormat:
MsgBox "The file you tried to import was not an Excel spreadsheet."
End Sub


Note: EmpDoc = access table name

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.