AnsweredAssumed Answered

Find the Number of Files in a Folder?

Question asked by Keaton Warn on Jun 13, 2018
Latest reply on Jun 13, 2018 by 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 =


     *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


     End Sub