Last modified date, folder path in a cell

Question

I have got a complex problem, it is more complex to explain, but let me start.

I have a path in an excel cell (path leads to a share) and i would like to do it in VBA.

I would like to get the file, which has the shortest last modified date (file was modified last in that folder), but it is not sure how many sub folder the folder I am scanning has.

My Question is, how do I Scan a folder for the files last modified date and how can i scan the subfolders (if there are subfolders) of this folder?

Manuel Allenspach Manuel Allenspach · Accepted Answer · 2013-10-17T13:53:55

Try this code:

Option Explicit

Public newestFile As Object

Sub start()
  Call getNewestFile("C:\yourpath")
  MsgBox newestFile.Name
End Sub

Private Sub getNewestFile(folderPath As String)
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object

    'get the filesystem object from the system
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(folderPath)


  'go through the subfolder and call itself
  For Each objFile In objFolder.SubFolders
    Call getNewestFile(objFile.Path)
  Next

  For Each objFile In objFolder.Files
    If newestFile Is Nothing Then
      Set newestFile = objFile
    ElseIf objFile.DateLastModified > newestFile.DateLastModified Then
      Set newestFile = objFile
    End If
  Next
End Sub

Last modified date, folder path in a cell

1 Answers