Import the worksheet to access as table

If you want to import the worksheet as table in access.

Download the file.




Here is the code-

Option Compare Database

Private Sub Command0_Click()
'coding for choose workbook button
Dim abc As New Excel.Application
Dim wbk As Excel.Workbook
Dim S As String
'reference select microsoft excel

S = abc.GetOpenFilename("Excel Files (*.Xls*), *.xls*")
Me.Text6.Value = S

abc.Visible = False
abc.ScreenUpdating = False
abc.DisplayAlerts = False
Set wbk = abc.Workbooks.Open(S)
' change the row source type of combo box to list value
For i = 1 To wbk.Sheets.Count
Me.Combo3.AddItem Sheets(i).Name
Next i


Me.Combo3.SetFocus
wbk.Close

abc.Visible = True
abc.ScreenUpdating = True
abc.DisplayAlerts = True

Set abc = Nothing
Set wbk = Nothing

End Sub

Private Sub Command8_Click()
'coding for import worksheet button
On Error Resume Next
DoCmd.RunSQL "DROP TABLE " & Me.Combo3.Value
DoCmd.TransferSpreadsheet acImport, , Me.Combo3.Value, Me.Text6.Value, True, Me.Combo3.Value & "!"


End Sub

Access Macro File http://www.filefactory.com/file/cc88827/n/import_from_excel.accdb

Excel File http://www.filefactory.com/file/cc87df9/n/import_to_access.xlsx