Can I import all excel files in a directory to Access

I have a database set up that will require bi-weekly updates of more than 30 spreadsheets. I have figured out how to create a macro to import one file and append it to my data table, but I cannot figure out how to import/append an entire directory of files at one time. It would be extremely manual/time consuming to import each file individually especially given the frequency of updates.

I've got my fingers crossed that there is a way to automate this process. Help!

Thanks for any advice/instructions you can offer!!

Have never done it, but I think you should look into TransferSpreadsheet Action. You will find information about it in Access help.

I have explored this option and it allows me to import one spreadsheet but not an entire directory.

For now, I have come up with a work-around using a long macro that will in succession import each file in the directory assuming I consistently name them. It was tedious to create, but for now it's a lot better than manually importing each file.

If you don't have VBA programming skills, it's best that you define one macro with the "TransferSpreadsheet" action of the macro as many times as needed. This is quick and dirty.

VBA can do it elegantly and in a sophisticated fashion through a form and a listbox in which you select spreadsheets randomly and the target table. However, this requires good knowledge of VBA and the Office reference library installed.

In either case, it's strongly recommended that the spreadsheets have identical field names (column names) and the data types are identical.

I figured that would be the case. I'm preparing to take some classes on VBA soon, so hopefully in the future I can revise my methods! For now, the work-around is doing what I need and it wasn't too difficult to set up. All of my spreadsheets have identical field names and data types as well as the same tab name which made setting up the macro go even more smoothly.

Thanks for all the responses!

Hi Lenore,
I wanted to know if you can send me that macro that imports one file and appends it to a data table.
My Email:hillel@glsecurities.com

Thanx.

Here is some code copied from the VBA help that uses Applciation.FileSearch that may be usefu.

Sub BatchImport()

With Application.FileSearch
.NewSearch
.LookIn = "C:\MyData"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks

If .Execute() > 0 Then 'Files found
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
'Transferspreadsheet
Next i
Else
MsgBox "There were no files found."
End If

End With 'End Filesearch

End Sub

Dan