Import excel sheet data into Ms-access using VBA macros

[gokul1242]

I have a excel file with three sheets named "A","B","C".
I have four columns: ID,Name_S,Age,City in all the sheets.

Now i want these data to be imported into a Ms-access database file.
I want three tables with their names same as the Sheet names ie.,A,B,C and also i want their field names as the column names.
I have used the following code to do this,but having few problems

1.I want only three columns to be imported ie.,Column B,C,D in all the sheets
2.I am not getting the field name as the column names i am getting field name as F1,F2 etc.,

Private Sub ImportXLSheets()

Dim WrksheetName As String
Dim i As Integer
Dim xl As Object
Set xl = CreateObject("Excel.Application")

xl.Visible = True
xl.Workbooks.Open "C:\Users\GOKULNAATH\Desktop\sa.xlsx"

With xl
.Visible = True
With .Workbooks(.Workbooks.Count)
For i = 1 To .Worksheets.Count
WrksheetName = .Worksheets(i).NAME
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel97, WrksheetName, "C:\Users\GOKULNAATH\Desktop\sa.xlsx"
Next i
End With

End With
Set xl = Nothing

End Sub

How to go about it ?
i have attached the sample excel file .also i have attached the access file image how i want the output to be.
View attachment Desktop.zip

 

[John Big Booty]

Welcome to the forum

Have a look at the sample posted here.

You will notice that the code used to import the excel data first imports the data into a temporary table where it is massaged to remove extraneous data prior to it being appended to the live table.