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_Cl ick

Set objFSO = CreateObject("S cripting.FileSy stemObject")

FolderToScan = "\\10.3.0.144\R SH_Log\DATA"

Set objFolder = objFSO.GetFolde r(FolderToScan)

NewestFile = ""
NewestDate = #1/1/1970#

For Each objFile In objFolder.Files
If objFile.DateLas tModified > NewestDate Then
NewestDate = objFile.DateLas tModified
NewestFile = objFile.Name
End If
Next
FileCopy NewestFile, "\\10.3.0.144\R SH_Log\DATA\430 0R TEST DATA PLOTS\import.tx t"
CurrentDb().Exe cute "DELETE * FROM tImport"
DoCmd.TransferT ext acImportDelim, "tImport Specification", "tImport", "\\10.3.0.144\R SH_Log\DATA\430 0R TEST DATA PLOTS\import.tx t", False
MsgBox "Data has been imported into the tImport table"
End If
Exit_Command6_C lick:
Exit Sub

Err_Command6_Cl ick:
MsgBox Err.Description
Resume Exit_Command6_C lick

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.