Importing from Excel into existing tables?

[Tasuki]

We have files (hundreds of them) with flat data in them stored on Sheets 2, 3, 4, and 5.

We have an access relational database with four tables.

We would like to import the Excel sheets into its respective tables.

The we have to continue with this process for each Excel file, appending the data from the next file/sheet into the proper Access table.

What would be the best way to deal with this data conversion/data loading situation?
Is there a way to automate any of these steps?

 

[cew657]

I have some questions. Is this process going to occur only once, or on a regular basis? How are the Excel files named? (IE. file1, file2, file3, file4...) How are the worksheets named? (IE. Sheet2, Sheet3, Sheet4, Sheet5).

cew657

 

[Tasuki]

It's a one-time process until all forms are loaded onto a database, afterwards, everything will be done through the database.

Excel files are named by their Form ID #, and worksheets are named when a new sheet is created, in this case, it's safe to assume that they'll be named .. Sheet1, Sheet2, Sheet3 .. almost always.

Any help, greatly appreciated.

T

 

[cew657]

Let me play around with a few things based on your reply. I will see what I can come up with.

cew657

 

[cew657]

Below is the code you will need. Sorry it took so long. We have had internet problems the last few days and I wasn't able to get back on. Anyway, what I did was create a form and then placed a button on this form. This is the code for my button.

What it will do is go to a specific directory and pull in all Microsoft Excel files in that directory. For each of those Microsoft Excel files, it will take sheets 2 through 5 and append them to the appropriate Microsoft Access tables called Import 2, Import 3, Import 4, Import5. For example, Sheet 2 will import to the table called Import2, Sheet 3 will import to the table called Import3, and so forth. The data will append to the appropriate table as it cycles through and picks up the files.

A word of caution. The Microsoft Excel sheets need to be the same for each sheet (IE the same number of columns) or else they will not append correctly. Also each Microsoft Excel workbook has to have Sheet2 through Sheet5 or else the macro will have problems.

You will need to change the ImportDir variable to reflect where you Microsoft Workbooks resides.

Once the macro is complete you can set up queries based on the tables of Import 2 through Import 5 to append to one table if need be.

HTH

cew657

Dim ImportFile As String, ImportDir, tblName As String, SheetName As Integer, Counter As Integer


ImportDir = "c:\test\"
ImportFile = Dir(ImportDir & "\*.xls")

Do While Len(ImportFile) > 0
    SheetName = 2
    For Counter = 1 To 4
            tblName = "Import" & SheetName
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tblName, ImportDir & ImportFile, False, "Sheet" & SheetName & "!"
            SheetName = SheetName + 1
    Next Counter
    ImportFile = Dir
Loop


MsgBox "Process Complete", vbInformation

 

[cew657]

I ran out of posting room. One more thing, you will need to change acSpreadsheetTypeExcel9 to the version of Microsoft Excel you are using.

cew657