Import data from all the worksheets to a single table in MS Access

If you want to merge data from all the worksheets in a workbook to single table in ms access database.

Download the access database file from the link-





Here is the code-


Option Compare Database

Private Sub Command0_Click()
Dim abc As New Excel.Application
Dim wbk As Excel.Workbook
Dim S As String
' refrence - micrososft excel

S = abc.GetOpenFilename("Excel Files (*.Xls*), *.xls*")
abc.Visible = False
abc.ScreenUpdating = False
abc.DisplayAlerts = False

On Error Resume Next
' it will delete if table already exists in access database
DoCmd.RunSQL "DROP TABLE " & Me.Text13.Value
Set wbk = abc.Workbooks.Open(S)
For i = 1 To wbk.Sheets.Count
If wbk.Sheets(i).UsedRange.Count >= 1 Then
DoCmd.TransferSpreadsheet acImport, , Me.Text13.Value, S, True, Sheets(i).Name & "!"
End If

Next i
abc.Visible = True
abc.ScreenUpdating = True
abc.DisplayAlerts = True
wbk.Close

Set abc = Nothing
Set wbk = Nothing
End Sub

Access file http://www.filefactory.com/file/cc87ed4/n/merge_all_worksheet_in_a_single_tables_in_access.accdb


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