Imorting file with most recent date

[MTSPEER]

Hello,

I'm trying to select the most recent file to import into my table with the most recent date. There will be other files in the folder. The code I have here will import the file, but it has to be named the same. I'm just looking to retrieve the file with the most recent date. Any suggestions? Thanks

1. Dim objFile As File
2. Dim objFolder As Folder
3. Dim objFSO As FileSystemObject
4. Dim FolderToScan As String
5. Dim NewestFile As String
6. Dim NewestDate As Date
7. Set objFSO = CreateObject("Scripting.FileSystemObject")
8. FolderToScan = "\\10.3.0.144\RSH_Log\DATA"
9. Set objFolder = objFSO.GetFolder(FolderToScan)
10. NewestFile = ""
11. NewestDate = #1/1/1970#
12. For Each objFile In objFolder.Files
13. If objFile.DateLastModified > NewestDate Then
14. NewestDate = objFile.DateLastModified
15. NewestFile = objFile.Name
16. End If
17. Next
18. CurrentDb().Execute "DELETE * FROM tImport", dbFailOnError
19. DoCmd.TransferText acImportDelim, "tImport Specification", "tImport", _
20. "\\10.3.0.144\RSH_Log\DATA\4300R TEST DATA PLOTS\import.txt", False
21. MsgBox "Data has been imported into the tImport table"

[June7]

Has to be same name every time because of the import specification? Why is that an issue? Name the file whatever you want.

[ssanfu]

I'm trying to select the most recent file to import into my table with the most recent date

What type of file? Text, CSV, Excel,....???

There will be other files in the folder.

What types of files? Same file type? Different file types? You might need another "IF...END IF" to check for the file extension before you check the file date.

You must be more specific with your question.


I am confused. You use the FSO to search the folder "\\10.3.0.144\RSH_Log\DATA", but you are importing from a different folder in the DoCmd.TransferText command: "\\10.3.0.144\RSH_Log\DATA\4300R TEST DATA PLOTS\import.txt"

Usually you import the file from the same folder you search in.

If the folder has ONLY text files and you want to import the latest file, concatenate the file name to the path:

Public Sub ImportFile()
    Dim objFile As File
    Dim objFolder As Folder
    Dim objFSO As FileSystemObject
    Dim FolderToScan As String
    Dim NewestFile As String
    Dim NewestDate As Date

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    FolderToScan = "\\10.3.0.144\RSH_Log\DATA"
    Set objFolder = objFSO.GetFolder(FolderToScan)
    NewestFile = ""
    NewestDate = #1/1/1970#
    For Each objFile In objFolder.Files
        If objFile.DateLastModified > NewestDate Then
            NewestDate = objFile.DateLastModified
            NewestFile = objFile.Name
        End If
    Next
    CurrentDb.Execute "DELETE * FROM tImport", dbFailOnError

    '--------------------------------------------
    '     testing - is the path and file name correct???
    MsgBox "The path and file name to be imported is " & vbNewLine & vbNewLine & FolderToScan & "\" & NewestFile
    '     Delete or comment out when testing is complete
    '--------------------------------------------

    '    DoCmd.TransferText acImportDelim, "tImport Specification", "tImport", "\\10.3.0.144\RSH_Log\DATA\4300R TEST DATA PLOTS\import.txt", False
    DoCmd.TransferText acImportDelim, "tImport Specification", "tImport", FolderToScan & "\" & NewestFile, False
    MsgBox "Data has been imported into the tImport table"


   Set objFSO = Nothing

End Sub