How to open a set of CSV files created on a certain date span?
Hello,
I have tons of CSV logs created daily, need to be able to specify a date range i.e "9/20/2021 - 9/23/2021" and open all the files created during this period.
Import them into an access table
This is the code I have pieced together from the web; however, I am unable to get it to work as it hangs up on the file open & import portion of it.
NOTE: I am looking for a method to accomplish this, so if you have a better way please do share
Thank you so much for your time
Code:
Dim oFile
Dim FSO As Object 'FileSystemObject
Set oFile = CreateObject("Scripting.FileSystemObject")
Dim Directory As String
Dim FileName As String
Dim varCellvalue As Long
Directory = "E:\Op60\"
If Right(Directory, 1) <> "\" Then
Directory = Directory & "\"
End If
FileName = Dir(Directory & "*.CSV")
Dim DateEarliest As Date
Dim EarliestDate As Date
EarliestDate = txt_Start_Date
DateEarliest = CDate(EarliestDate)
Dim DateLatest As Date
Dim LatestDate As Date
LatestDate = txt_Start_Date2
DateLatest = CDate(LatestDate)
MsgBox (DateEarliest)
MsgBox (DateLatest)
Do While FileName <> ""
'**-*-*-*-*-*-*-*-*
Debug.Print oFile.getFile(Directory & FileName).DateCreated, EarliestDate, LatestDate
'**** HERE COMMENTED OUT LINE BY LINE & EACH GAVE ME A SYNTAX ERROR
'*** SO I HAD TO COMMENT THEM ALL OUT
'if oFile.getFile(Directory & FileName).DateCreated >= DateEarliest and oFile.getFile(Directory & FileName).DateCreated <= DateLatest
' DoCmd.TransferText TransferType:=acLinkDelim, tableName:="CsvTbl", _
'
' OpenFile = Directory & FileName, HasFieldNames:=True
' FileName:="E:\Op60\" + MyTbl + Myextension, HasFieldNames:=True
FileName = Dir
Loop
