Get most recent file in a folder and then import it with transfertext into a table.

I have the transfer text portion working fine, and I can select the most recent file in excel vba, but I can't get the most recent file portion of the code to work in access vba.

It ends with error "compile error variable not defined"

I'm a bit new at vba, could anyone help?


Private Sub Command6_Click()
On Error GoTo Err_Command6_Click

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
FileCopy NewestFile, "\\10.3.0.144\RSH_Log\DATA\4300R TEST DATA PLOTS\import.txt"
CurrentDb().Execute "DELETE * FROM tImport"
DoCmd.TransferText acImportDelim, "tImport Specification", "tImport", "\\10.3.0.144\RSH_Log\DATA\4300R TEST DATA PLOTS\import.txt", False
MsgBox "Data has been imported into the tImport table"
End If
Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub

Try:

One very important note that I missed is that: You must set a Reference to the Microsoft Scripting Runtime Object Library in order for this code to work.

Thank you, the Reference and the code were both very helpful.