take an existing file and save it to a generic name like "c:\data\file2import.xls"
attach this file as an external table
build a query to append the excel table to your data table.
the code below will scan the folder, copy the excel file to overwrite the generic one, then run the import query, repeat.
Code:
''usage on button click:
sub button_click()
ImportAllFilesInDir "c:\folder\"
end sub
Public Sub ImportAllFilesInDir(ByVal pvDir)
Dim vFil, vTargT, vTarg
Dim i As Integer
Dim fso
Dim oFolder, oFile
Dim vOutFile
On Error GoTo errImp
If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"
vTarg = "c:\data\File2Import.xls"
docmd.setwarnings false
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(pvDir)
For Each oFile In oFolder.Files
vFil = pvDir & oFile.Name
If InStr(vFil, ".xls") > 0 Then 'ONLY DO excel FILES
FileCopy vFil, vTarg '' docmd.transferspreadsheet vFil
docmd.openquery "qaImportXL"
End If
Next
Set fso = Nothing
Set oFile = Nothing
Set oFolder = Nothing
MsgBox "Done"
Exit Sub
errImp:
MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
Exit Sub
Resume
End Sub