Results 1 to 2 of 2
2007-07-09, 13:53 #1
- 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.
2007-07-09, 14:34 #2
- 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.
' 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
' Delete temporary table
DoCmd.DeleteObject acTable, "tblTTTT"
strFile = Dir
MsgBox Err.Description, vbExclamation
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.