Open file where modified date prior to current day and latest time that day?

JanMantins · Jul 13th 2016

Here's a list of what I've pull out using a modified version of the 3rd example on this page [ link ]

Code

Sub Iterate_Folders()
Dim ctr As Integer
ctr = 1
Path = "C:\StuffTest"
FirstDir = Dir(Path, vbDirectory)
Do Until FirstDir = ""
If (GetAttr(Path & FirstDir) And vbDirectory) = vbDirectory Then
ActiveSheet.Cells(ctr, 1).Value = Path & FirstDir
ctr = ctr + 1
End If
FirstDir = Dir()
Loop
End Sub

Display More

Results - pulling out filename, modified date, difference between modified date and previous business date as double , and previous business date as double.
[ATTACH=CONFIG]69665[/ATTACH]

Is there a smarter way to select only the most recent file where the modified date is NOT today, but the MAX date from that range? ( highlighted greeny-yellow in example)

I've tried loading the dates into an array and sorting, and casting both the modified date and previous business dates as doubles to find the difference , but I think I'm probably doing it in the most long winded way.

I may be going about this in a more complicated than necessary fashion - since implementing a scripted save, I am saving the date and time within the filename, so I *could* use that instead - it's just trying to get a reliable logic where it'll still capture the most recent reports ( run for prior 2 business days or more ) if the user had forgotten to run it for a week/month.

**Ger Plante** · Jul 13th 2016

Re: Open file where modified date prior to current day and latest time that day?

Not entirely clear to me what you are doing, it seems you want the most recent file that was modified BEFORE today... ?

why not load all modified dates as "double" into an array (except todays dates) and just use the worksheetfunction.max function on the an array to return the latest one?

Or count the number of files have a modified date of today and use worksheetfunction.large(array,n) where n the nth largest number (n would be the total number of files less the number of files from today)....

There are probably a few more ways to to do it... but ultimately you have to read each files properties to get the details.

Ger

M40wen · Jul 13th 2016

Re: Open file where modified date prior to current day and latest time that day?

I think you have to loop through all the files like you are doing...

Code

Directory = "C:\StuffTest\"
f = Dir(Directory, vbNormal)
   
   
dte = FileDateTime("C:\StuffTest\" & f)
   
Do While f <> ""
If dte > FileDateTime(Directory & f) Then
dte = FileDateTime(Directory & f)
End If
f = Dir()
Loop

Display More

JanMantins · Jul 13th 2016

Re: Open file where modified date prior to current day and latest time that day?

Thanks for the replies guys.

I guess I was just trying to do it as cleanly as possible without having to create items on a worksheet.

What I did was extract it all to a hidden sheet and use a maxifs on the difference, then an index/match on that value to return the filename for that figure.

We're all on Excel 365 ( v16 ) so should be okay.

M40wen · Jul 13th 2016

Re: Open file where modified date prior to current day and latest time that day?

Using my sample you don't need to worry about adding sheets etc. Your query I guess was is there a way of finding the newest date instantly (like "max"), but all the files have to be read to find the timestamp anyway. Id just get the timestamp and then refresh it if its later until the end of the directory loop, but your way will work too.

Open file where modified date prior to current day and latest time that day?

Participate now!

Share