Import multiple Excel files into Access

I have a bunch of individual Excel files. They are all contained in the same folder. Each one only has a few records. They all have the same format (data in the first 5 columns only). I'm looking for an import program that will import all the files in the folder so that I don't have to manually do a "get external data" for each individual file since there are like a 100 of them and I will be having to do this task frequently in the future.  Or what also would be fine is a macro to combine all of the excel files into one big file. Either way is fine.... a program in Access or a program in Excel. Thanks.
dbfromnewjerseyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Hello dbfromnewjersey,

Assuming you want all Excel files in a given folder...

Dim fso As Object, fld As Object, fil As Object

Set fso = CreateObject("Scripting.FIleSystemObject
Set fld = fso.GetFolder("c:\folder\subfolder")

For Each fil In fld.Files
    If LCase(Right(fil.Name, 4)) = ".xls" Then
        DoCmd.TransferSpreadsheet TransferType:=acImport, _
            TableName:= "NameOfTable", FileName:=fil.Path, _
            HasFieldNames:=True
    End If
Next

Set fil = Nothing
Set fld = Nothing
Set fso = Nothing

Regards,

Patrick
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbfromnewjerseyAuthor Commented:
I put that code under a command button on a form, changed the to the right of GetFolder and changed TableName to my table name. When I run the code, nothing happens. Any thoughts?
0
Patrick MatthewsCommented:
dbfromnewjersey,

Please paste back the code exactly as you implemented it.

Regards,

Patrick
0
dbfromnewjerseyAuthor Commented:
Now I'm getting an error saying: "Run-time error 2391".  Field 'F1' doesn't exist in destination table tblTEST.  I should mention that my excel files do not have field names/headers. The data starts on row 1 of each file. There is nothing in the folder other than the files I need to import. I also tried commenting out the "If Then" logic since the only things in the folder are excel files. Here is the code under the command button:

Private Sub Command0_Click()
Dim fso As Object, fld As Object, fil As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder("F:\TESTFOLDER")
For Each fil In fld.Files
    If LCase(Right(fil.Name, 4)) = ".xls" Then
        DoCmd.TransferSpreadsheet TransferType:=acImport, _
            TableName:="tblTEST", FileName:=fil.Path, _
            HasFieldNames:=False
    End If
Next

Set fil = Nothing
Set fld = Nothing
Set fso = Nothing

End Sub
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.