Import multiple xls files into 1 table (2003)

Topic

New Reply

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.

Reply | Quote

Replies

In post 659,661, 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 [" & tblImport & "] 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

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.

Reply | Quote