[VBA] - Check for latest report folder within a directory.

[RockandGrohl]

Hello all,

We have a folder for a customer with reporting folders inside, so typically each customer folder will look like this:

2020-12
2021-01
2021-02
2021-03
2021-04
etc
2021-07
2021-08
[other folders]
[other files]

What I want to do is to get the latest dated folder that has the YYYY-MM format, ignoring all other files and folders.

So once that is found, it can be added to a string to open that particular folder like:

Company\Customers\Donk\2021-08\

I'd then want to open any xls file that had the name "Usage" in it.

Thanks guys!

 

[Akuini]

How would this know that 2021-08 is later than 2021-03 for instance?

Sorry, actually I maybe have a wrong assumption.
I think that Dir function always return a sorted list by name, so I just need to get last folder with "####-##" format.
but I just read this discussion:
Does Dir() make any guarantee on the order of files returned?
There's no guarantee that Dir() will return the files in any particular order. The MS Access VBA documentation even says:

Tip Because file names are retrieved in no particular order, you may want to store returned file names in an array, and then sort the array.

but it only mention about files not folder. So I'm not sure it also true with folder.
If you want I think I can amend the code to get list sorted, maybe by using Arraylist

@Akuini, your code gives me an empty string. This can be explained by the fact that a file attribute usually consists of multiple attributes.

Sorry, I don't understand, my code is only to get the list of folder not files. Could you elaborate?

@Akuini's alternative works for now, but only on the basis of coincidences. This has to do with how the NTFS file system works.
In any case, change the = with the AND operator because once your folder has been indexed, @Akuini's code won't work anymore.

Wow, you have a deep understanding about this subject. Thanks for sharing.

 

[RockandGrohl]

Don't worry, if you are happy, so am I.

@Akuini's alternative works for now, but only on the basis of coincidences. This has to do with how the NTFS file system works.
In any case, change the = with the AND operator because once your folder has been indexed, @Akuini's code won't work anymore.

Which "=" should I change? Cheers.

 

[Akuini]

Here's the amended code;
In case there's no guarantee that Dir() will return the files in any particular order, I sort the list using ArrayList object.

Sub GetSubFolderNames_1180678b()
Dim FileName As String
Dim PathName As String
Dim dar As Object

PathName = "D:\zzz\try\" '< --adjust
FileName = Dir(PathName, vbDirectory)
   
Set dar = CreateObject("System.Collections.ArrayList")
    
Do While FileName <> ""
    If GetAttr(PathName & FileName) = vbDirectory And FileName Like "####-##" Then dar.Add FileName
    FileName = Dir()
Loop

dar.Sort
Debug.Print dar.Item(dar.Count - 1)

End Sub

 

[GWteB]

Sorry, I don't understand, my code is only to get the list of folder not files. Could you elaborate?

A folder is a special kind of file. It has attributes like files have. Attributes are on low-level particular (binary) bits which are set or cleared. The VBA vbDirectory constant equals 16, meaning bit 4 is set, all other bits are cleared. In cases the content of a file or a folder is not indexed, its "not content indexed" attribute is set, meaning bit 13 is set, equals 8192. If no other attributes ar set, like Archive, Hidden or Read-Only, such folder's attribuut equals 8208. Since 16 <> 8208 your code fails if some other attributes are set as well.

Which "=" should I change?

If GetAttr(PathName & FileName) AND vbDirectory And FileName Like "####-##" Then tx = FileName

In case there's no guarantee that Dir() will return the files in any particular order, I sort the list using ArrayList object.

1) There's no guarantee!
2) Sorting is what my code does, precisely because of 1)

 

[Akuini]

@GWteB
Thanks for this valuable input.

 

[GWteB]

You're welcome.