Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
Thanks for your concern ! I have same format(with numbers no texts) of data with 20 columns and the number of it is 366 sheets in one file(.xls),then I want to put them in multiple tables in access database , it's very often that I need to import the sheets to a database so I need a code?or button in excel vba ! –  blueman010112 Jun 29 '11 at 12:56
    
@blueman010112 if all the sheets are always going to be in one workbook add VBA code to the workbook, otherwise if multiple workbooks are involved, then create an Excel addin instead. –  Miles D Jun 29 '11 at 18:32

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.)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.