put the code below into a module.
(press ctl-G, on the menu bar, click INSERT, MODULE. paste the code, save as module1)
to run the code,
create a new macro
for the action type RUNCODE
for the function name type: LoadAllTextFiles()
save
Code:
'----------------
function LoadAllTextFiles()
'----------------
ScanAllFilesInDir "C:\folder\"
end function
'----------------
Public Sub ScanAllFilesInDir(ByVal pvDir)
'----------------
Dim vFil, vTargT
Dim i As Integer
Dim fso
Dim oFolder, oFile
Dim vTbl
On Error GoTo errImp
If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(pvDir)
For Each oFile In oFolder.Files
vFil = pvDir & oFile.Name
If InStr(vFil, ".dat") > 0 Then 'ONLY DO .DAT FILES
Import1TextFile vFil
'DELETE THE FILE HERE
kill vFil
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
'the source file is copied to a generic file everytime
'----------------
Public Sub Import1TextFile(ByVal pvFile)
'----------------
Dim vDir, vTarg
'make a copy
vDir = "c:\ImportFiles\"
MakeDir vDir
vTarg = vDir & "File2Import.txt"
'import data
FileCopy pvFile, vTarg
DoCmd.TransferText acExportDelim, "specName", "table", vTarg, True
End Sub
'----------------
Public Sub MakeDir(ByVal pvDir)
'----------------
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(pvDir) Then fso.CreateFolder pvDir 'MkDir pvDir
Set fso = Nothing
End Sub