Consulting

Results 1 to 2 of 2

Thread: How to open a set of CSV files created on a certain date span?

  1. #1
    VBAX Newbie
    Joined
    Sep 2021
    Posts
    1
    Location

    Question 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


    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

  2. #2
    VBAX Regular
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    86
    Location

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •