Unreachable If statement?

[Jw4096]

So I am hoping this is a simple issue since I am new to VBA and don't really fully understand how to work with it yet.

Goal: Creating a function which can be given a directory and then will find the most recently modified file in that directory and all its sub directories.

Problem: The If statement which evaluates if a file is newer than the current newest file is never reached. I placed a breakpoint in the subroutine to make sure it was being called and it was but the breakpoint on the If statement never runs.

Code:

Dim FSO As Scripting.FileSystemObjectDim FileItem As Scripting.File
Dim FolderItem As Scripting.Folder
Dim MostRecentPath As String
Dim MostRecentFileName As String
Dim MostRecentDate As Date


Public Function GetMostRecentFile(directory As String)


    Set FSO = New Scripting.FileSystemObject
  
    ProcessFolders (directory)
    
    GetMostRecentFile = MostRecentFile & "," & MostRecentFileName & "," & MostRecentDate
    
    MostRecentPath = ""
    MostRecentFileName = ""
    MostRecentDate = ""
 
End Function


Public Sub ProcessFolders(folderDirectory As String)
    
    Dim targetFolder As Scripting.Folder
    Set targetFolder = FSO.GetFolder(folderDirectory)
    
    For Each FolderItem In targetFolder.SubFolders
        ProcessFolders (FolderItem)
    Next FolderItem
    
    For Each FileItem In targetFolder.Files
        ProcessFile (FileItem)
    Next FileItem
    
End Sub


Public Sub ProcessFile(fileDirectory As String)
    
    Dim targetFile As Scripting.File
    Set targetFile = FSO.GetFolder(fileDirectory)
    
[U][B]    If targetFile.DateLastModified > MostRecentDate Then[/B][/U]
        MostRecentPath = targetFile
        MostRecentFileName = targetFile.Name
        MostRecentDate = targetFile.DateLastModified
    End If
    
End Sub

Other Information:

The line which could not be reached is bold and underlined

To run the above code you need to make sure the Microsoft Scripting Runtime is referenced!

I had it set up so that to use the function you would enter =GetMostRecentFile(B1) where B1 contained something similar to: C:\Users\user\Desktop\TestFolder

I am currently using Microsoft Excel 2010

Also any tips on working with VBA macros or better ways of doing things I have done are greatly appreciated.

Thanks for your time.

 

[RoryA]

Hi, and welcome to the forum.

Couple of issues:
1. This line:

Set targetFile = FSO.GetFolder(fileDirectory)

should be this:

Set targetFile = FSO.GetFile(fileDirectory)

2. In your main function, this line:

   GetMostRecentFile = MostRecentFile & "," & MostRecentFileName & "," & MostRecentDate

should be:

   GetMostRecentFile = MostRecentPath & "," & MostRecentFileName & "," & MostRecentDate

3. You can't assign "" to a Date variable.

Also, were you intentionally evaluating the arguments you pass to the two subs to get the folder/file names from the Folder/File objects, or was that luck?

 

[Jw4096]

Thanks for the reply and the welcome

Ah **** I knew there would be some stuff like that in there. I don't suppose there is built in re factoring anywhere?

For assigning a date "" what should I use instead? I am guessing something like "01/01/1900" I tried using "Nothing" for all three of them but that wouldn't compile.

As for the evaluating the names if you meant passing in the paths to the subs then it was intentional otherwise it was probably luck.

Here is the updated code:

Dim FSO As Scripting.FileSystemObject
Dim FileItem As Scripting.File
Dim FolderItem As Scripting.Folder
Dim MostRecentPath As String
Dim MostRecentFileName As String
Dim MostRecentDate As Date


Public Function GetMostRecentFile(directory As String)


    Set FSO = New Scripting.FileSystemObject
  
    MostRecentPath = ""
    MostRecentFileName = ""
    MostRecentDate = 1 / 1 / 1900
    
    ProcessFolders (directory)
    
    GetMostRecentFile = MostRecentPath & "," & MostRecentFileName & "," & MostRecentDate
 
End Function


Public Sub ProcessFolders(folderDirectory As String)
    
    Dim targetFolder As Scripting.Folder
    Set targetFolder = FSO.GetFolder(folderDirectory)
    
    For Each FolderItem In targetFolder.SubFolders
        ProcessFolders (FolderItem)
    Next FolderItem
    
    For Each FileItem In targetFolder.Files
        ProcessFile (FileItem)
    Next FileItem
    
End Sub


Public Sub ProcessFile(fileDirectory As String)
    
    Dim targetFile As Scripting.File
    Set targetFile = FSO.GetFile(fileDirectory)
    
    If targetFile.DateLastModified > MostRecentDate Then
        MostRecentPath = targetFile
        MostRecentFileName = targetFile.Name
        MostRecentDate = targetFile.DateLastModified
    End If
    
End Sub

This does seem to work but is there anything you would recommend like stopping the screen updating?

Thanks again for your time.