How do I find the most recently created Folder in a specified directory/

markjlynch used Ask the Experts™
Hi. I have seen several questions along the same lines as my problem but I can't seem to get anything to work for me. I hope someone can help me out.

I have a series of files copied into a unique folder each evening which is named according to the date the files are placed in it. So if the files were copied into a folder on May 7th 2010 the folder name would be 070510 and located off of a variable directory path which I would like to specifiy in a function call.  I want to be able to write to this location as well as read files from that location based on the date the most recent folder was created.

An example would be that there are 20 subfolders off of my main directory - which I would pass via a function such as getlastfolder("C:\temp") - which would then return the folder name of the most recently created folder. Suppose the most recent folder was created 8 days ago on 2nd October 2010 then I can then use that string variabe (021010) later to locate certain files I need to write or read from in my other code.

It sounds simple and no doubt it is but I'm going around in circles! I will be using this code within VBA routines in Excel and Access 2007.

Much appreciate any help you can give me with this. I hope 125 points is worthy of the effort required to solve this.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Try this, it is a bit rickety, but it seems to work.
It is based on the fact that the loop will start at the newest Folder.

'You must set a reference to the "Microsoft Scripting Runtime" Library in your VBA Editor
Dim fso As FileSystemObject
Dim fldrRoot As Folder
Dim SubFld As Folder
Dim strFoldeName As String
Dim strFullFldrPath As String

Set fso = New FileSystemObject
Set fldrRoot = fso.GetFolder("c:\YourFolder\")

For Each SubFld In fldrRoot.SubFolders
    strFoldeName = SubFld.Name
    strFullFldrPath = fldrRoot & "\" & SubFld.Name
    MsgBox "Full Folder Name and Path: " & strFullFldrPath
    MsgBox "Folder Name Only: " & strFoldeName
    MsgBox "Folder Date: " & SubFld.DateCreated
    Exit For
Next SubFld

I am sure you can tweak this to fit your needs (for ex.: concatenate the trailing slash, if need be)


There is another approach that does not rely on navigating the folder structure. Assuming that the folders are creating using the strict formula of "mmddyy" you could test for each day starting with today and then move back 1 day until you found the folder.
The DIR() function returns the first file in a folder or if the second parameter is vbDirectory, it will return the directory name if it exists.

Public Function FindLatestFolder(ByVal strRoot As String) As String
On Error GoTo ERR_Handler
' The parameter strRoot is the "parent" folder of mmddyy folder
Dim strPath As String
Dim dtFolderDate As Date
Dim i As Integer

    ' Start with today's date
    dtFolderDate = Date
    'Construct the Initial folder for today
    strPath = strRoot & Format(dtFolderDate, "mmddyy") & "\"
    ' Loop thru the prior 30 days
    '(you can use some other method to limit the loop)
    For i = 1 To 30
        'Dir returns the folder if it exists
        If Len(Dir(strPath, vbDirectory)) > 0 Then
            ' If the folder exist return the complete path
            FindLatestFolder = strPath
            GoTo EXIT_Handler
        End If
        'Try the previous day
        dtFolderDate = dtFolderDate - 1
        ' Construct the path to be tested
        strPath = strRoot & Format(dtFolderDate, "mmddyy") & "\"
    Next i

    Exit Function
    MsgBox "Error in FindLatestFolder"
End Function

Bob Ullrich


Worked great thank you very much Bob.
FYI I needed to search for the folders based on a European naming format of ddmmyy so simply changed the formatting to suit this. Other than that is worked first time. Now to bang my against a wall on another problem no doubt.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial