EXCEL sheets batch input to Access I have a lot of Excel datasheet (in one file)

I want to batch input to Access 

but I can't find a sample code to do it

the name of datasheet in access is same as in excel

is there any code of VBscript can do it?

maybe in access or excel vba?

or any simple software?
I would use SSIS to do this. Try these steps:


I would start out by creating an object variable to store your datasheets' names
I would then create a VB task to pull out the names of your spread sheets and put the results into the object variable  
Then create a foreach loop that uses the object variable as the collection
create another variable and toss in the name of the current sheet
add a data flow task inside the loop that uses the the current sheet as your data source
add your access table as your destination.


Here is the VB script you can use to pull out the excel metadata. You need to input the variables from your foreach loop to get this to work.

   Public Overrides Sub CreateNewOutputRows()

    Dim excelFilePath As String = Me.Variables.ExcelFilePath.ToString()
    Dim strCn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" &amp; _
            "Data Source=" + excelFilePath + ";Extended Properties=Excel 8.0"
    Dim dtTables As DataTable
    Dim tableName As String

    Dim cn As OleDbConnection = New OleDbConnection(strCn)
    cn.Open()
    dtTables = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

    For Each row As DataRow In dtTables.Rows
        tableName = row("TABLE_NAME").ToString()

        OutputBuffer.AddRow()
        OutputBuffer.EXCELFILEPATH = excelFilePath
        OutputBuffer.TABLENAME = tableName

    Next

    cn.Close()
    OutputBuffer.SetEndOfRowset()
End Sub


It's a bit tricky but very do-able. Best of luck.