the code below will load every xl book in the folder,
it joins the book as an external table,
then a query will check col J (it needs a name, not a col#) for the date < today
if so, import.
(you do not need: Dim XL as Excel.Application)
you DO need a query: qaImportXLwithPriorDatesOnly", to import records whos date < today
usage:
ImportFilesInDir "your folder here"
Code:
Public Sub ImportAllFiles()
ImportFilesInDir "c:\temp\"
End Sub
Public Sub ImportFilesInDir(ByVal pvDir)
Dim FSO, oFolder, oFile, oRX
Dim sTxt As String, sFile As String
Const kTARG = "C:\temp\File2Import.xlsx"
Const kTBL = "xlFile2Import"
Dim vDat As Date
On Error GoTo errGetFiles
Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(pvDir)
If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"
For Each oFile In oFolder.Files
If InStr(oFile.Name, ".xls") > 0 Then 'import file here
sFile = oFile
'remove old xl table
CurrentDb.TableDefs.Delete kTBL
'connect to next workbook
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, kTBL, sFile, True
'if you dont want to impor the wb at all if any record > today
vDat = Dlookup("[dateFld]",kTBL, "[DateFld]>=#" & date() & "#")
if IsNull(vDat) then 'no dates > today, so import
DoCmd.OpenQuery "qaImportXLwithPriorDatesOnly"
endif
'else
' 'query to import ONLY records with date < today
' DoCmd.OpenQuery "qaImportXLwithPriorDatesOnly"
End If
Next
endit:
Set oFile = Nothing
Set oFolder = Nothing
Set FSO = Nothing
Exit Sub
errGetFiles:
If Err = 3265 Then 'catch error if NO Import table errors
' "no errors found"
Resume Next
Else
MsgBox Err.Description, , Err
End If
End Sub