Import only most recent file using FileSearch & LastModified

  • Thread starter Thread starter Gerwin
  • Start date Start date
G

Gerwin

Hello,


I am trying to write code for Access 2003 that only imports the most recent
..xls-file in some directory, however, whatever I do it always selects the
first file that matches the file name searched for. This file is not
necessarily even a file that was modified today although I specified
otherwise.

What am I doing wrong here?


Thanks for any help!

---

Const MySourceDir As String = "C:\IMPORT\"
Const MySourceFile As String = "FileToImport"
Const MyTargetTable As String = "tblTargetTable"

With Application.FileSearch
.NewSearch
.LookIn = MySourceDir
.LastModified = msoLastModifiedToday
.SearchSubFolders = False
.FileName = MySourceFile
.MatchTextExactly = True
If .Execute(SortBy:=msoSortByLastModified,
SortOrder:=msoSortOrderDescending) > 0 Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8,
MyTargetTable , .FoundFiles(1), True
MsgBox "File imported properly."
Else
MsgBox "No recent file found to import."
End If
End With
 
I have found the error myself already: I set a reference to the Microsoft
Office 12.0 Object library and now it works flawlessly.


Gerwin
 
Back
Top