This imports all files in the folder,
usage:
ImportAllFilesInDir "c:\folder"
Code:
'------------
Public Sub ImportAllFilesInDir(ByVal pvDir)
'------------
Dim vFil, vTargT
Dim i As Integer
Dim sTbl As String, sSql As String
Dim FSO
Dim oFolder, oFile
DoCmd.SetWarnings false
On Error GoTo errImp
If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"
sTbl = "tData"
Set db = CurrentDb
Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(pvDir)
For Each oFile In oFolder.Files
vFil = pvDir & oFile.Name
If InStr(sfile, ".xls") > 0 Then 'ONLY DO EXCEL FILES
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, sTbl, vFil, True
end if
Next
Set FSO = Nothing
Set oFile = Nothing
Set oFolder = Nothing
DoCmd.SetWarnings True
Exit Sub
errImp:
MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
End If
Exit Sub
Resume
End Sub