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

[moke123]

Also how can you have a Date Created after a Date Modified?

I think thats Copy and paste.

 

[Isaac]

I have never seen that before!

Definitely happens. I just downloaded a file from one of my client's FTP sites. Due to the way my FTP client happens to handle this task, the date created shows the moment I downloaded it. But the date modified shows 15 minutes earlier.

Can be common in file transfer situations, depending on how the transfer client interacts with Windows.

 

[moke123]

try commenting out the "If" and the "end if" lines and see if it lists all the files.

Set fso = New FileSystemObject

    If Len(Dir(strFolder, vbDirectory)) = 0 Then Exit Function

    Set fol = fso.GetFolder(strFolder)

    For Each fil In fol.Files

        'If fil.DateCreated >= dteStart And fil.DateCreated <= dteEnd Then

            lbx.AddItem Format(fil.DateCreated, "Short Date") & ";" & fil.Path
          
        'End If

    Next

 

[NahualXl]

Definitely happens. I just downloaded a file from one of my client's FTP sites. Due to the way my FTP client happens to handle this task, the date created shows the moment I downloaded it. But the date modified shows 15 minutes earlier.

Can be common in file transfer situations, depending on how the transfer client interacts with Windows.

just looked at the original folder and it shows the same issue with the dates haha

 

[NahualXl]

try commenting out the "If" and the "end if" lines and see if it lists all the files.

Set fso = New FileSystemObject

    If Len(Dir(strFolder, vbDirectory)) = 0 Then Exit Function

    Set fol = fso.GetFolder(strFolder)

    For Each fil In fol.Files

        'If fil.DateCreated >= dteStart And fil.DateCreated <= dteEnd Then

            lbx.AddItem Format(fil.DateCreated, "Short Date") & ";" & fil.Path
         
        'End If

    Next

tried it & there are 53 files created on 9/24/2021 and it shows only 2. & 300+ on 9/16/2021.. it does not look like it is loading all the 9/16/2021 either

 

[NahualXl]

@NahualXl
This is your application and you can do whatever you want but I'm telling you from experience, that at some point your users will either miss files or double import them if you persist in using this technique. It is an accident waiting to happen.

I understand your point; however, I have not clue as how to code the approach you are suggesting

 

[moke123]

I understand your point; however, I have not clue as how to code the approach you are suggesting

Here's another example which has some of Pat's ideas in it, although a little less robust.
It adds a field to the record listing the file the data is from and the date and time of the import.
It moves the file to the archive folder after copying it. ( you can click the reset button to move them back just for the demo purposes)
I also defaulted the dates and folder path just to make it easy. Just click the submit button.
Also included a few example excel files.

 

[NahualXl]

Hello, Updated OP with updated code, just need some minor tweaks... appreciate everyone's time and assistance

 

[moke123]

On my way out to work, I'll take a look when I get there.
Meanwhile here's another demo with a seperate log file which records the range of primary keys for each file imported.
I made it the other day but never got a chance to post it.

 

[moke123]

If strFile.DateCreated > (txt_Start_Date - 1) And strFile.DateCreated < (txt_Start_Date2 + 1) Then

What tweaks?

if dates are not working it could be you're comparing a date to a string.
You'll also need fso to get the date created property.

 

[NahualXl]

If strFile.DateCreated > (txt_Start_Date - 1) And strFile.DateCreated < (txt_Start_Date2 + 1) Then

What tweaks?

if dates are not working it could be you're comparing a date to a string.
You'll also need fso to get the date created property.

"error" invalid qualifier here -> strFile.DateCreated

 

[moke123]

"error" invalid qualifier here -> strFile.DateCreated

yea, you need to use fso to get the created date