Import All worksheet of a workbook to Access Database as New Table

If you want to import all the worksheets from a workbook as a table into MS Access database.For example you are having 4 sheets in a workbook "North" ,"South","West", and "East" and you want to import data from each worksheet into access and create new tables with same sheet names "North" ,"South","West", and "East"

Download the ms access file and click on the command button snapshot below




Here is the code-


Option Compare Database

Private Sub Command0_Click()
' Reference Microsoft excel
Dim abc As New Excel.Application
Dim wbk As Excel.Workbook
Dim S As String
S = abc.GetOpenFilename("Excel Files (*.Xls*), *.xls*")
abc.Visible = False
abc.ScreenUpdating = False
abc.DisplayAlerts = False
Set wbk = abc.Workbooks.Open(S)
For i = 1 To wbk.Sheets.Count
On Error Resume Next
'it will delete the table if its already existing in access database
DoCmd.RunSQL "DROP TABLE " & Sheets(i).Name
DoCmd.TransferSpreadsheet acImport, , Sheets(i).Name, S, True, Sheets(i).Name & "!"
Next i
abc.Visible = True
abc.ScreenUpdating = True
abc.DisplayAlerts = True
wbk.Close
Set abc = Nothing
Set wbk = Nothing
End Sub

Access Macro http://www.filefactory.com/file/cd17f4a/n/import_all_worksheet_as_tables_in_access.accdb

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