VBA to get Largest file in folder

Question

I try this code to get largest file. But it loop and not works. Please help me to process with it. Thank you

Sub FindLargestSignature()
      Dim SignaturePath As String
      Dim SignatureFile As String
      Dim fso As Object
      Dim fs As Object
      Dim S As Long
      Dim LargeFile As String
      Dim LargeSize As Long
      
      Set fso = CreateObject("Scripting.FileSystemObject")
      
      
      SignaturePath = Environ("appdata") & "\Microsoft\Signatures\"
      SignatureFile = Dir(SignaturePath & "*.htm*", vbNormal)
      If Len(SignatureFile) > 0 Then
        Set fs = fso.getfile(SignaturePath & SignatureFile)
        LargeSize = fs.Size
        LargeFile = SignatureFile
      End If
      
      Do
        SignatureFile = Dir()
        If Len(SignatureFile) > 0 Then
          Set fs = fso.getfile(SignaturePath & SignatureFile)
          S = fs.Size
          If S > LargeSize Then
            LargeSize = S
            LargeFile = SignatureFile
          End If
        Else
          Exit Do
        End If
      Loop
      
      MsgBox "The Largest file is named: " & LargeFile & " and is " & LargeSize & " bytes"
        
      
End Sub

Answer 1

You can simplify greatly by sticking to the Microsoft Scripting Runtime library and the FileSytemObject functions.

Public Sub FindLargestSignature()
    Dim fso As New FileSystemObject
    
    Dim SignaturePath As String
    SignaturePath = fso.BuildPath(Environ("appdata"), "\Microsoft\Signatures\")
    
    Dim SignatureFolder As Folder
    Set SignatureFolder = fso.GetFolder(SignaturePath)
    
    Dim f As File
    Dim LargeFile As String
    Dim LargeSize As Long
    n = SignatureFolder.Files.Count
    LargeSize = 0
    For Each f In SignatureFolder.Files
        If fso.GetExtensionName(f.Path) = "htm" Then
            If f.Size > LargeSize Then
                LargeSize = f.Size
                LargeFile = f.Name
            End If
        End If
    Next
    ' LargeSize and LargeFile contain the details of the largest "htm" file

End Sub

---

Here is a more general version in the form of a function, that may include an extension filter (for example only "htm" files), and the ability to transverse subfolders.

It returns a File object from the largest file it finds. The test routine here is FindLargestSignature that calls the function FindLargestFile to find the largest signature.

Global fso As New FileSystemObject

Public Sub FindLargestSignature()
    Dim SignaturePath As String
    SignaturePath = fso.BuildPath(Environ("appdata"), "\Microsoft\Signatures\")
    
    Dim SignatureFolder As Folder
    Set SignatureFolder = fso.GetFolder(SignaturePath)
    
    Dim LagestFile As File
    Set LagestFile = FindLargestFile(SignatureFolder, "htm", True)
    
    ' LargestFile contains the details of the largest "htm" file
    Debug.Print LagestFile.Name

End Sub

Public Function FindLargestFile(ByVal InPath As Folder, Optional Filter As String = "*", Optional IncludeSubFolders As Boolean = False) As File
        
    Dim f As File, d As Folder
    Dim LargeFile As String
    Dim LargeSize As Long
    n = InPath.Files.Count
    LargeSize = 0
    LargeFile = vbNullString
    For Each f In InPath.Files
        If Filter Like fso.GetExtensionName(f.Path) Then
            If f.Size > LargeSize Then
                LargeSize = f.Size
                LargeFile = f.Path
            End If
        End If
    Next
    
    If IncludeSubFolders Then
        For Each d In InPath.SubFolders
            Set f = FindLargestFile(d, Filter, IncludeSubFolders)
            If f.Size > LargeSize Then
                LargeSize = f.Size
                LargeFile = f.Path
            End If
        Next
    End If
        
    Set FindLargestFile = fso.GetFile(LargeFile)

End Function

---

Don't forget to add the following reference to the project (under Tools/References).

Answer 2

There are a couple things I would do with this code:

1. I would get rid of Dir and only use FileSystemObject
2. I would have the function return a File

With these changes, the code becomes more simplistic:

Option Explicit

Private Sub Test()
   Dim f As File
   Set f = FindLargestSignature(Environ("appdata") & "\Microsoft\Signatures\", "*.htm*")
   
   If Not f Is Nothing Then
      MsgBox "The Largest file is named: " & f.Name & " and is " & f.Size & " bytes"
   End If
End Sub

Private Function FindLargestSignature(ByVal SignaturePath As String, ByVal SignatureFile As String) As File
   Dim fso As FileSystemObject
   Dim gf As Folder
   Dim sf As Folder
   Dim gff As File
   Dim sff As File
   Dim LargestSize As Long
   
   Set fso = New FileSystemObject
   Set gf = fso.GetFolder(SignaturePath)
   
   For Each gff In gf.Files
      If gff.Name Like SignatureFile Then
         If gff.Size > LargestSize Then
            LargestSize = gff.Size
            Set FindLargestSignature = gff
         End If
      End If
   Next
   
   For Each sf In gf.SubFolders
      Set sff = FindLargestSignature(gf.Path & "\" & sf.Name, SignatureFile)
   
      If Not sff Is Nothing Then
         If sff.Size > LargestSize Then
            LargestSize = sff.Size
            Set FindLargestSignature = sff
         End If
      End If
   Next
End Function

EDIT:

In order to handle a folder with any number of nested folders, the function needs to be called recursively. The function has been modified to loop through SubFolders, calling itself for every folder encountered.