Get_CountFiles function Ms Access Gurus
Home Code

VBA > Function > Count Files

Count the number of files in a specified folder path.

Screen shot

Files in a folder, and the results of a function that calls GetCountFiles.

Files in a directory

Possible Uses:

Dimension an Array

Get the number of files in a folder so you can dimension an array.

Message for User

Perhaps you are looping through a folder and importing files. The number of files to process could be used in a message to the user. Each time through the loop, the message could be changed to also show what number is being processed, and perhaps the file name too.

Progress for processing files in a folder

Code


' module name: mod_File_GetCountFiles
'*************** Code Start *****************************************************
' Purpose  : Count the number of files in a folder
' Author   : crystal (strive4peace) 
' Return   : Long
' License  : below code
' Code List: www.MsAccessGurus.com/code.htm
'--------------------------------------------------------------------------------

'                              GetCountFiles
'--------------------------------------------------------------------------------
Function GetCountFiles(psPath As String) As Long
'strive4peace 
'uses Late Binding. Reference for Early Binding:
'  Microsoft Scripting Runtime
   'PARAMETER
   '  psPath is folder to get the number of files for
   '     for example, c:\myPath
   ' Return: Long
   '    -1 = path not valid
   '     0 = no files found, but path is valid
   '    99 = number of files where 99 is some number
   
   'inialize return value 
   GetCountFiles = -1 
   'skip errors    
   On Error Resume Next
   'count files in folder of FileSystemObject for path   
   With CreateObject("Scripting.FileSystemObject")
      GetCountFiles = .GetFolder(psPath).Files.Count
   End With
End Function
'
' LICENSE
'   You may freely use and share this code 
'     provided this license notice and comment lines are not changed;
'     code may be modified provided you clearly note your changes.
'   You may not sell this code alone, or as part of a collection,
'     without my handwritten permission.
'   All ownership rights reserved. Use at your own risk.  
'   ~ crystal (strive4peace)  www.MsAccessGurus.com
'*************** Code End *******************************************************

'--------------------------------------------------------------------------------

'                              call_GetCountFiles_MsgBox
'--------------------------------------------------------------------------------
Sub call_GetCountFiles_MsgBox ()
   'click HERE and press F5 to run
   Dim sPath As String
   sPath = "c:\myPath"    '------------- customize 
   MsgBox Format(GetCountFiles(sPath), "#,##0") _
      & " files in " & sPath _ 
      , , "GetCountFiles"
End Sub
'--------------------------------------------------------------------------------

'                              call_GetCountFiles_BadPath
'--------------------------------------------------------------------------------
Sub call_GetCountFiles_BadPath ()
   'click HERE and press F5 to run
   Dim sPath As String _
      , nCount As Long
      
   sPath = "c:\invalid"
   nCount = GetCountFiles(sPath)
   
   If nCount < 0 Then
      MsgBox sPath & " is not valid" _
         , , "GetCountFiles"
   Else
      MsgBox Format(nCount, "#,##0") _
         & " files in " & sPath _
         , , "GetCountFiles"
   End If
End Sub

Logic

Assign the function return value to be -1 to indicate that the passed folder path isn't valid.

Skip all errors.

Use With to create a temporary instance of FileSystemObject so space for a variable that needs to be released isn't allocated.

Late binding is used so that the VBA project doesn't have to reference the Microsoft Scripting Runtime library.

Get the number of files using the .Files.Count property of GetFolder for the given path (psPath).

Because no storage is set aside for an object variable, there is not one to release.

This VBA can be used in applications other than Access ... Excel, Part, PowerPoint, Project, Visio, ...

Parameter

psPath

Backstory

Ocassionally, Dir() won't get a correct filename if it has Unicode characters. After experimenting, I saw that the FileSystemObject had better success -- at least I didn't find any failures. I found that out using the code posted here to loop and rename files in a folder

In that program, the initial ReDim for the filename array was a guess; this function gives a better number, and also tests if the path is valid. Even though not all files in that code may match the mask (couldn't find a property for the FileSystemObject given a path and file mask — so if you know a way other than iterating, please email info at msaccessgurus.com with your code so it can be shared), it might* still better than using the chosen number.

* and hence, why updated code for LoopRenameDate isn't posted ... haven't done any time testing.

The idea is to post this code, then a function to return an array of filenames in a path, then update the procedure to rename files. For now, I'm posting GetCountFolders since that is handy if you want to expand folders while you iterate.

Reference - Help

CreateObject object

Docs / Office VBA Reference / Language Reference / Reference / Functions / CreateObject

Help: CreateObject function

Although the Help article doesn't discuss (as of this writing) releasing object variables when they are declared with Dim and then assigned to the return of CreateObject, is is good practice to Close or Quit anything you Open and assign to an object variable, and then set the object variable to nothing when you're done.

The above code uses With instead of declaring and using an object variable, but is still creating the object for the code to temporarily use. Performance might be better by declaring a module object variable and re-using it. In that case, it should also be released.

FileSystemObject object

Docs / Office VBA Reference / Language Reference / Reference / Objects / FileSystemObject object

Help: FileSystemObject object

GetFolder method

Docs / Office VBA Reference / Language Reference / Reference / Objects / FileSystemObject object / Methods / GetFolder

Help: GetFolder method

Files collection

Docs / Office VBA Reference / Language Reference / Reference / Objects / File object / Files collection

Help: Files collection

With statement

Docs / Office VBA Reference / Language Reference / Reference / Statements / With

Help: With statement

Reference - Articles

How To Use FileSystemObject with Visual Basic

Help Article: How To Use FileSystemObject

scrrun.dll defines the FileSystemObject ... the Drive object, the Folder object, the File object, and the TextStreamObject object.

Share

Share with others ... here's the link to copy:
https://MsAccessGurus.com/VBA/Code/File_CountFiles.htm

Do you have something to say or share?

It is interesting to hear from you. Was something not clear? Did you find a bug? Is an explanation wrong or not sufficient? Do you want the code do more (there is always more)?

Some of you write to say thanks and tell me what you're doing with Access ... its nice to get an echo back. I want you and others to be good with Access, and other Office applications like Excel, Word, and PowerPoint ... and Windows. Take advantage of the strengths in each to manage your information wisely.

Are you a developer? Do you want to share? Email to ask about getting your pages added to the code index.

When we communicate, collaborate, and appreciate, we all get better. Thank you. Email me at info@msAccessGurus.com

Goto Top