Best Way To Import a Variable File? (1 Viewer)

JoeGKushner

New member
Local time
Today, 08:31
Joined
Jun 23, 2015
Messages
9
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
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"
 

Peter Reid

Registered User.
Local time
Today, 13:31
Joined
Dec 3, 2003
Messages
134
Yep, it looks like everything is in that code for you to do what you've asked.

Why not make your changes and try it and let us know the outcome?
 

JoeGKushner

New member
Local time
Today, 08:31
Joined
Jun 23, 2015
Messages
9
Okay, after some messing around, I've managed to get it to work to a point that I'll have to fine tune the actual import steps to verify that the file is similar enough every day that it doesn't decide half of what's in there is garbage.

Thanks for the extra eyes everybody!

If someone can mark this as solved? Is that something I can do?

EDIT: Okay, now to the next problem, I only want it to look at specific files starting with the first 4 characters. Any ideas?
 
Last edited:

Users who are viewing this thread

Top Bottom