Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Ephrata, Washington, USA
    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
    Thanked 28 Times in 28 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.
    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

    Exit Sub

    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    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