Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import multiple xls files into 1 table (2003)

    We have about 400 Excel files, all single-worksheet files, all formatted exactly the same in one folder.

    What's the easiest way to pull the data from all 400 files into one Access table.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import multiple xls files into 1 table (2003)

    In <post:=659,661>post 659,661</post:>, you mentioned that the extension of the files is .dbf. This means that they are DBase files, not Excel spreadsheets.

    Start by importing one (any) of the .dbf files into Access using File | Get External Data | Import...
    Rename it; in the following code I have assumed that you named it tblImport.
    Open the imported table, and delete all records, then close it again.
    Press Alt+F11 to activate the Visual Basic Editor.
    Select Insert | Module.
    Copy the following code (a variant of the code from the Excel thread) into the module.
    <code>
    Sub MergeFiles()
    ' Path - modify as needed but keep trailing backslash
    Const strPath = "C:Excel"
    ' Arbitrary name for temporary table
    Const strTemp = "tblTTTT"
    ' Name of target table - modify as needed.
    Const strTarget = "tblImport"
    Dim strFile As String
    Dim strSQL As String

    On Error GoTo ErrHandler

    strSQL = "INSERT INTO <!t>[" & tblImport & "]<!/t> SELECT * FROM " & strTemp
    strFile = Dir(strPath & "*.dbf")
    Do While Not strFile = ""
    ' Import into temporary table
    DoCmd.TransferDatabase acImport, "dBase 5.0", strPath, acTable, strFile, strTemp
    ' Transfer records
    CurrentDb.Execute strSQL
    ' Delete temporary table
    DoCmd.DeleteObject acTable, "tblTTTT"
    strFile = Dir
    Loop

    ExitHandler:
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </code>
    Modify the path (keeping the backslash at the end!) and the name of the imported table.
    Click anywhere in the code, and press F5 to run it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •