Best Way To Import a Variable File?
On a daily basis, I am importing a file whose name changes every day. It's based on when the time the creation date for the file finishes so it's DDOPHEE_201512310613.TXT one day and DDOPHEE7_201512310620.TXT another day.
I was wondering if I could use VBA through a Module to look in the specific folder/location, which never changes, and use the File system object properties, to grab the newest TXT file, if it matches today's date in terms of it's Date created properties, regardless of it's name?
I've seen some code that looks like it might cover a bit of this but am not sure if it would do everything I'm looking for. I know I'd have to change the folder location for example and some other bits. Any feedback?
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
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"