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

[arnegrant]

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

[ADezii]

Try:

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
 
FileCopy NewestFile, "\\10.3.0.144\RSH_Log\DATA\4300R TEST DATA PLOTS\import.txt"
 
CurrentDb().Execute "DELETE * FROM tImport", dbFailOnError
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"

[ADezii]

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.

[arnegrant]

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