Count files in a folder

[XLEAccessGuru]

I need one of my access apps to count the number of files stored in a specific folder/directory and compare that number to another. Does anyone know any code that will count the number of files in a directory/folder?

Any/all help would be greatly appreciated. This is a VERY urgent issue.



Thanks!

 

[petehilljnr]

Give this a whirl - you can call it from anywhere.
e.g. files = Count_Files("C:\MyFolder")

Regards,
Pete.

Function Count_Files(strFolder as String) as Long

On Error Goto Error_Handler

Dim file_count as Long
Dim file_name as String

file_name = Dir(strFolder)
file_count = 0

Do While file_name <> ""

file_count = file_count + 1
file_name = Dir

Loop

Count_Files = file_count

Exit Function

Error_Handler:
Debug.Print Err.Description

Count_Files = -1

Exit Function

 

[MarkK]

Or you can use a FileSystemObject from the Microsoft Scripting Runtime.

Function GetFileCount(folderspec As String) As Integer
[COLOR="Green"]'  Returns a count of files in folderspec, or -1 if folder does not exist[/COLOR]
   Dim fso As Object
   Set fso = CreateObject("Scripting.FileSystemObject")
   If fso.FolderExists(folderspec) Then
      GetFileCount = fso.GetFolder(folderspec).Files.Count
   Else
      GetFileCount = -1
   End If
End Function

Cheers,

 

[CharlesWhiteman]

This code works fine, can/how could it be modified to also look into and includes files in sub-directories?

 

[MarkK]

This'll do either ...

Function CountFiles(folderspec As String, Optional includeSubFolders As Boolean) As Long
   Dim fso As New Scripting.FileSystemObject
   
   'Set fso = CreateObject("Scripting.FileSystemObject")
   If fso.FolderExists(folderspec) Then
      If includeSubFolders Then
         CountFiles = CountFilesRecursive(fso.GetFolder(folderspec))
      Else
         CountFiles = fso.GetFolder(folderspec).Files.Count
      End If
   Else
      CountFiles = -1
   End If
   
End Function

Private Function CountFilesRecursive(folder As Scripting.folder) As Long
   Dim fd As Scripting.folder
   Dim tmp As Long
   
   tmp = tmp + folder.Files.Count
   For Each fd In folder.SubFolders
      tmp = tmp + CountFilesRecursive(fd)
   Next
   CountFilesRecursive = tmp

End Function

To late bind replace Scripting types with Object, and uncomment the CreateObject line.

 

[SmartCarFun]

Or you can use a FileSystemObject from the Microsoft Scripting Runtime.

Function GetFileCount(folderspec As String) As Integer
[COLOR=green]'  Returns a count of files in folderspec, or -1 if folder does not exist[/COLOR]
   Dim fso As Object
   Set fso = CreateObject("Scripting.FileSystemObject")
   If fso.FolderExists(folderspec) Then
      GetFileCount = fso.GetFolder(folderspec).Files.Count
   Else
      GetFileCount = -1
   End If
End Function

Cheers,

in Access 2010, i paste this into a module? How do i call it?

I create folders based on a field name, example....

Private Sub Command429_Click()
If Len(Dir("[URL="file://\\MAMA\shares\IW_CASHAPPS\RECORDS\STORE\"]\\MAMA\shares\IW_CASHAPPS\RECORDS\STORE\[/URL]" & Me![RemitReference], vbDirectory)) = 0 Then
     MkDir "[URL="file://\\MAMA\shares\IW_CASHAPPS\RECORDS\STORE\"]\\MAMA\shares\IW_CASHAPPS\RECORDS\STORE\[/URL]" & Me![RemitReference]
 End If
Dim x As Variant
x = Shell("EXPLORER.EXE [URL="file://\\MAMA\shares\IW_CASHAPPS\RECORDS\STORE\"]\\MAMA\shares\IW_CASHAPPS\RECORDS\STORE\[/URL]" & Me![RemitReference], vbNormalFocus)
End Sub

This code looks for a folder and creates one if there, then opens it.

What I'm trying to do is when the main form opens it counts the files in this directory. so I could create a list of records and quickly see how many files are in each directory....

Thanks

 

[MarkK]

Here's a code calling that function...

Sub Test123
  const FOLDER as string = "C:\Some\Folder\Name\"
  dim fileCount as long

  fileCount = GetFileCount(FOLDER)

  msgbox "There are " & fileCount & " files in " & FOLDER, vbInformation

End Sub

Making sense?

 

[SmartCarFun]

Thanks, but is there anyway for it to appear in a field on a form/report instead of a message box?

 

[MarkK]

Me.MyControl = GetFileCount("C:\Some\Folder\")

... or in the ControlSource property of a control...

=GetFileCount([SomeFieldOnYourFormThatContainsAFolderName])

Yeah?