importing all files in a folder (all excel files) into a single table in an access database

Question

I am trying to import a bunch of excel 2003 files all with A:H columns and they are under the same headings etc. into a table in access 2003 database. This is a module in access im making. Im using a file search to look for every file that begins with Format (which they all do ) to get at all the files in the folder path. is there a more efficient way to do this? somehow select all files in a folder? and import each to the same table in access? The DoCmd.TransferSpreadsheet seeems to take each file path individually so I'm not sure how to get each file name in the folder to import it.

If you can solve this problem, or if you have any reccommendations as to a better way of doing this or anything that would be great! thank you in advance =)

I have this at the moment:

Sub Import()

Dim db As Database
Set db = CurrentDb

Dim appendtbl As Recordset
Set appendtbl = db.OpenRecordset("sampletbl", dbOpenDynaset)

 Dim FilePathString As String
Dim folderString As String

folderString = "C:blahblahblah"
Dim lngFileNumber As Long
With Application.FileSearch
    .NewSearch
    .LookIn = folderString
    .FileType = xls
    .Filename = "Format"
    If .Execute > 0 Then

    For lngFileNumber = 1 To .FoundFiles.Count

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "sampletbl",       FilePathString, True, "A:H"

    Next lngFileNumber

    End If
    End With

    End Sub

I did some googling after i tried writing the code and foudn some much more efficient lookign ways such as :

strFileName = Dir("somepath\*.XLS")
Do Until strFileName = ""
'import from file "somepath\" & strFileName
strFileName = Dir()
Loop

I'm gonna try it out and see how it goes

Answer 1

You can do that using the FileSystemObject.

Dim oFs As New FileSystemObject
Dim oFolder As Folder
Dim oFile As File
If oFs.FolderExists(FullPath) Then
    Set oFolder = oFs.GetFolder(FullPath)

    For Each oFile In oFolder.Files

    //oFile contain the file information

For more information on the FSO check out the MSDN docs: http://msdn.microsoft.com/en-us/library/6tkce7xa%28v=VS.85%29.aspx