3 Replies Latest reply on Jun 13, 2018 1:42 PM by Keaton Warn

    Find the Number of Files in a Folder?

    Keaton Warn

      I want a macro to find the number of files in a folder so it can make sure that the file being saved won't accidentally overwrite a preexisting file in the specified folder. If there is an easier way to stop accidental file overwrites when saving a file from a macro, please enlighten me. Lines with an asterisk * in front are just English expressions of what that section of code will do. This might have something to do with dir() but I've only just started reading about that.


      I need to understand how the "*Find Number of Files in Folder" code will work and if the For loop will be adequately fast so it doesn't take forever to do this if it is run in a folder with a lot (>1000, probably more around 10000) files.



           Option Explicit


           Dim NumOfFiles As Long

           Dim strSaveNameAs As String

           Dim FileName() As String



           strSaveNameAs = Bracket.zip


           *Find Number of Files in Folder


           NumOfFiles = [number of files in the folder determined in the code above]


           For i= to UBound(NumOfFiles)

                If FileName(i) = strSaveNameAs

                     MsgBox "File cannot be saved since there is already a file with the same name."


               End If

           Next i


           *Save file under the name Bracket.zip


           End Sub