EXCEL sheets batch input to Access

Question

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?

Answer 1

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;" & _
            "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.

Answer 2

The simplest way I can think of is probably writing some VBA code in the Excel workbook. How much effort that is depends on factors like how many columns you have in each sheet, what format the data is in, how similar each sheet is, and how often you are going to have to do this. Does all the data from each sheet go in to a one table or multiple tables in the Access database? Another option could be run write some code to export each data to a CSV files, combine and then import into Access.

Answer 3

I would strongly recommend going the CSV route.

Exporting from Excell to CSV is simply a matter of doing a "Save As...". Importing it into Access is a pretty simple matter as well. (If that link dies, there's many more like it on Google.)