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

[NahualXl]

Are there files created within the date range in the folder?

Yes see attachments please. This is what I see on the DB when I press submit nothing happens

 

[Gasman]

That is a very narrow range? Try something bigger as it definitely works.
Also how can you have a Date Created after a Date Modified?

 

[NahualXl]

That is a very narrow range? Try something bigger as it definitely works.
Also how can you have a Date Created after a Date Modified?

View attachment 94789

LoL you have noticed that as well I have never seen that before! that is why I said date created is the best way to go about it. I picked the range of 9/15-9/24 and see the attachment for the results. it only displays the 9/16/2021 date files and nothing else. I do have a lot of files created between these dates thou

 

[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

 

[Pat Hartman]

@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.

 

[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

 

[Pat Hartman]

I explained it earlier but you weren't interested. we are now 30 posts into this thread so maybe you want to figure out what is wrong with what you have. Then you can decide if you want to dump all the current procedure and start again. Meanwhile, I'll see if I have an example.

 

[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