none
How to find the most recently updated directory and then open a file inside that directory? RRS feed

  • Question

  • Good Afternoon Amazing People!

    I have in the past created a macro to find and open the most recently modified file in a directory. Now I have a challenge where I need to find the most recently modified directory, enter it and then find the most recently modified file.

    Below is the macro to open the file...

    <BEGIN CODE>

                

    Sub OpenLatestDataDetails()



    Dim ofile As Object
    Dim dteMax As Date
    Dim oFoundFile As Object
    Dim oFold As Object
    Dim FSO As Object
    Dim strFileName As String
    Set FSO = CreateObject("scripting.filesystemobject")
    'change folder to suit
    Set oFold = FSO.GetFolder("Z:\Reports\SyncFromProd\SopafDetails")
    dteMax = 0
    For Each ofile In oFold.Files
    x = ofile.Name
    y = ofile.datelastmodified
    Z = ofile.Type
    With ofile

        If .Type = "Microsoft Excel 97-2003 Worksheet" Or .Type = "Microsoft Excel Worksheet" Then
             If .Name Like "*Details*" And .datelastmodified > dteMax Then
                Set oFoundFile = ofile
                dteMax = .datelastmodified
            End If
        End If

    End With
    Next ofile
    Workbooks.Open oFoundFile.Path, ReadOnly:=True
    Set FSO = Nothing

    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        strFileName = ActiveWorkbook.Name
        Windows("PSC_Dashboard-2017-Draft-1.xlsb").Activate
        Sheets("Details").Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False


        Workbooks(strFileName).Close

    End Sub

    <END CODE>

    How would I accomplish finding the last modified directory?

    Thank You.

    Thursday, March 16, 2017 5:52 PM

Answers

  •             

    Hello again, Bill,
    re:  newest directory/file

    Code below should find the latest folder on the drive/parent folder.
    (it seems to work, but I didn't check the dates on every folder)

    Be aware that Windows is protective of the System Volume Folder (located on the main drive(s)).
    In my commercial "Files or Folders" workbook, the program skips that folder.
    (worth downloading)
    Also, you will have to insert or run your own find file code once the newest folder is found.

    Note that the GetSubFolders function uses recursion to iterate all subfolders.
    (it makes multiple calls to itself in order to scan the subfolders in subfolders)

    '---
    Sub FindTheNudePhotosOfLassie()
     MsgBox GetNewestSubFolder("C:\Program Files")
    End Sub
    '---

    Function GetNewestSubFolder(ByRef strPath As String) As String
       'Jim Cone - Portland, Oregon USA - May 16, 2015
        Dim oFSO              As Object
        Dim oFolder          As Object
        Dim oSubFldr        As Object
        Dim strFolderPath As String
        Dim dteDate       As Date

        Set oFSO = CreateObject("Scripting.FileSystemObject")
        Set oFolder = oFSO.GetFolder(strPath)

        For Each oSubFldr In oFolder.Subfolders
           If oSubFldr.DateLastModified > dteDate Then
             dteDate = oSubFldr.DateLastModified
             strFolderPath = oSubFldr.Path
           End If
           Call GetSubFolders(oSubFldr, dteDate, strFolderPath)
        Next
        GetNewestSubFolder = strFolderPath & vbCr & "Last modified:  " & dteDate

        Set oSubFldr = Nothing
        Set oFolder = Nothing
        Set oFSO = Nothing
    End Function
    '----

    Function GetSubFolders(ByRef oParentFolder As Object, dtDate As Date, _
             strWhere As String) As String
        Dim oSubFolder As Object
        For Each oSubFolder In oParentFolder.Subfolders
           If oSubFolder.DateLastModified > dtDate Then
             dtDate = oSubFolder.DateLastModified
             strWhere = oSubFolder.Path
           End If
           Call GetSubFolders(oSubFolder, dtDate, strWhere)
        Next 'oSubFolder
    End Function

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Friday, March 17, 2017 5:28 AM

All replies

  •             

    Hello again, Bill,
    re:  newest directory/file

    Code below should find the latest folder on the drive/parent folder.
    (it seems to work, but I didn't check the dates on every folder)

    Be aware that Windows is protective of the System Volume Folder (located on the main drive(s)).
    In my commercial "Files or Folders" workbook, the program skips that folder.
    (worth downloading)
    Also, you will have to insert or run your own find file code once the newest folder is found.

    Note that the GetSubFolders function uses recursion to iterate all subfolders.
    (it makes multiple calls to itself in order to scan the subfolders in subfolders)

    '---
    Sub FindTheNudePhotosOfLassie()
     MsgBox GetNewestSubFolder("C:\Program Files")
    End Sub
    '---

    Function GetNewestSubFolder(ByRef strPath As String) As String
       'Jim Cone - Portland, Oregon USA - May 16, 2015
        Dim oFSO              As Object
        Dim oFolder          As Object
        Dim oSubFldr        As Object
        Dim strFolderPath As String
        Dim dteDate       As Date

        Set oFSO = CreateObject("Scripting.FileSystemObject")
        Set oFolder = oFSO.GetFolder(strPath)

        For Each oSubFldr In oFolder.Subfolders
           If oSubFldr.DateLastModified > dteDate Then
             dteDate = oSubFldr.DateLastModified
             strFolderPath = oSubFldr.Path
           End If
           Call GetSubFolders(oSubFldr, dteDate, strFolderPath)
        Next
        GetNewestSubFolder = strFolderPath & vbCr & "Last modified:  " & dteDate

        Set oSubFldr = Nothing
        Set oFolder = Nothing
        Set oFSO = Nothing
    End Function
    '----

    Function GetSubFolders(ByRef oParentFolder As Object, dtDate As Date, _
             strWhere As String) As String
        Dim oSubFolder As Object
        For Each oSubFolder In oParentFolder.Subfolders
           If oSubFolder.DateLastModified > dtDate Then
             dtDate = oSubFolder.DateLastModified
             strWhere = oSubFolder.Path
           End If
           Call GetSubFolders(oSubFolder, dtDate, strWhere)
        Next 'oSubFolder
    End Function

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Friday, March 17, 2017 5:28 AM
  • This worked perfectly! Thank you!

    Loved the humorous labels by the way... LOL!

    Thursday, March 23, 2017 2:54 PM