Count # of CERTAIN files in a folder but not one by one (1 Viewer)

[CedarTree]

Hello... looking to count the numder of TIF files in a folder (thousands of folders really) without going by one file by one file within each folder. I saw some promising code but I get #VALUE in Excel or some kind of error each time. Suggestions?

Here's code I have to count ALL files in a folder (works just fine)... other code I've seen wants to use CMD prompt and/or namespace functions -- stuff I'm not super comfy with. Thanks!

Function fnCountFilesInFolder(pPath As String) As Long
On Error Resume Next

    Dim fso As Object
    Dim objFiles As Object
    Dim obj As Object
      
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set objFiles = fso.GetFolder(pPath).Files
    If Err = 76 Then
        Err = 0
        fnCountFilesInFolder = -1 'record -1 files if the folder doesn't exist
    Else
        fnCountFilesInFolder = objFiles.Count
    End If
  
    Set objFiles = Nothing
    Set fso = Nothing
    Set obj = Nothing
  
End Function

 

[theDBguy]

Not sure if this will help, but you could try using the FolderExists method instead of On Error Resume Next.

FolderExists method (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

In the GetFolder method, are you using a wildcard for pPath?

 

[The_Doc_Man]

It is the nature of the file system object - and the nature of collections - that they count one at a time, using loops to step through them all.

There is a roundabout way to do this but it is SO convoluted that you might be doing better, logic-wise, to simply write the loop.

One method would be to use the CMD prompt to issue a sequence of commands ending with a DIR command to make a list of files using options to remove header and trailer and the > syntax to redirect the output to a file and the /S option to include subdirectories, leaving you with a file that lists all file names in that folder and all folders beneath it. Then you can import that list to a table and run a query to count the number of files that contain the required file type. This is ugly in several ways, since you then need to dispose of the table AND the file that you used as an intermediary. It is an instant recipe for database bloat, which is also not good. If the DIR file's syntax is right, you COULD just try to rename the file to .CSV and map it, perhaps saving a little bit of bloat. But this wouldn't give you counts by folder, it would only give you total for ALL folders at once.

 

[MarkK]

looking to count the numder of TIF files in a folder (thousands of folders really) without going by one file by one file within each folder.

How do know a file matches your criteria if you don't check them all?

 

[ebs17]

Public Function ListFilesToFile(ByVal StartDirectory As String, _
                                ByVal PathOutputFile As String, _
                                Optional ByVal FileExtension As String = "*") As Boolean
' Output the list of full paths to a text file

    With CreateObject("WScript.Shell")
        .Run "cmd.exe /c dir " & StartDirectory & "\*." & FileExtension & " /b/s >" & PathOutputFile & Chr(34)
    End With
    
    ListFilesToFile = True
End Function

' #####################################################

' call
ListFilesToFile pPath, "D:\log.txt", "tif"

You then only have to count the lines in the text file, e.g. by

UBound(Split(ReadFile("D:\log.txt"), vbCrLf)) + 1

 

[moke123]

With an FSO folder object you can easily access the folders file count property as shown in the Debug.Print line below.

If your folders contain file types other than just TIFF files you would need to iterate through each folder , and use the
fso.GetExtensionName method to only count the TIFF files. If there are subfolders in the folders you would need to make it recursive to include the subfolders.

Sub slistfolders(strFolder, Optional IncludeSubFolders As Boolean = True)

    '    Dim fso As New FileSystemObject
    '    Dim fol As Folder,  sfol As Folder

    Dim fso As Object
    Dim fol As Object, sfol As Object
    Set fso = CreateObject("Scripting.filesystemobject")

    Set fol = fso.GetFolder(strFolder)

    For Each sfol In fol.SubFolders

            Debug.Print sfol.Name, sfol.Files.Count
        
        If IncludeSubFolders = True Then
      
            slistfolders sfol.Path, IncludeSubFolders
        
        End If
        
    Next

End Sub

 

[Gasman]

Perhaps use the command line
Dir *.tiff > tiff.txt

Then read that file and get the number at the bottom.?

 

[MajP]

See if this is useful

Solved - Access VBA - Loop through files in Folders, Subfolders, Sub-Sub Folders....

Hi, Before I write my own nested nested nested loop, surely someone has something out there already.... I need to list all the files in a single 'base' folder, which includes subfolders of the base folder. and subfolders of the subfolder's subfolders. The nesting of the subfolders is assumed...

www.access-programmers.co.uk

 

[CedarTree]

Not sure if this will help, but you could try using the FolderExists method instead of On Error Resume Next.

FolderExists method (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

In the GetFolder method, are you using a wildcard for pPath?

No - for now, pPath = the folder I'm counting files in. But perhaps if I pass in a wildcard, e.g., C:\Folder\*.TIF ???

EDIT: Sorry - that doesn't seem to work. You can't do *.TIF within a folder reference.

 

[CedarTree]

Public Function ListFilesToFile(ByVal StartDirectory As String, _
                                ByVal PathOutputFile As String, _
                                Optional ByVal FileExtension As String = "*") As Boolean
' Output the list of full paths to a text file

    With CreateObject("WScript.Shell")
        .Run "cmd.exe /c dir " & StartDirectory & "\*." & FileExtension & " /b/s >" & PathOutputFile & Chr(34)
    End With
 
    ListFilesToFile = True
End Function

' #####################################################

' call
ListFilesToFile pPath, "D:\log.txt", "tif"

You then only have to count the lines in the text file, e.g. by

UBound(Split(ReadFile("D:\log.txt"), vbCrLf)) + 1

I tweaked it but I'm getting an error on the last line (on read file):

Function fnCountFilesInFolderWithFilter(pPath As String, Optional pMask As String = "*") As Long

    Const csPathOutputFile = "D:\log.txt"

    With CreateObject("WScript.Shell")
        .Run "cmd.exe /c dir " & pPath & "\*." & pMask & " /b/s >" & csPathOutputFile & Chr(34)
    End With
  
    fnCountFilesInFolderWithFilter = UBound(Split(ReadFile(csPathOutputFile), vbCrLf)) + 1
  
End Function

 

[CedarTree]

With an FSO folder object you can easily access the folders file count property as shown in the Debug.Print line below.

If your folders contain file types other than just TIFF files you would need to iterate through each folder , and use the
fso.GetExtensionName method to only count the TIFF files. If there are subfolders in the folders you would need to make it recursive to include the subfolders.

Sub slistfolders(strFolder, Optional IncludeSubFolders As Boolean = True)

    '    Dim fso As New FileSystemObject
    '    Dim fol As Folder,  sfol As Folder

    Dim fso As Object
    Dim fol As Object, sfol As Object
    Set fso = CreateObject("Scripting.filesystemobject")

    Set fol = fso.GetFolder(strFolder)

    For Each sfol In fol.SubFolders

            Debug.Print sfol.Name, sfol.Files.Count
       
        If IncludeSubFolders = True Then
     
            slistfolders sfol.Path, IncludeSubFolders
       
        End If
       
    Next

End Sub

How would I use GetExtensionName to include only TIFs? Thanks.

 

[ebs17]

ReadFile is an user-defined function. You would have to explicitly include this in your VBA project.
At least the name adequately describes what the function does.

VB-Tec / Daten / Dateien / Einlesen - Beliebige Datei einlesen

Beliebige Datei einlesen... Visual Basic-Technik, Beratung, Support

vb-tec.de

 

[MajP]

How would I use GetExtensionName to include only TIFs? Thanks.

It does not help in trying to do this without reading file by file. But if reading file by file this works.

Private Function GetMatchingFiles(SourceFolderFullName As String, Optional FileExtension As String = "All") As Collection

' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
    Dim FSO As Object 'Scripting.FileSystemObject
    Dim col As New Collection
    Dim SourceFolder As Scripting.Folder 'Scripting.Folder
    Dim SubFolder As Scripting.Folder 'Scripting.Folder
    Dim FileItem As Scripting.file 'Scripting.File
    Dim ext As String
   ' Dim ParentID As Long
    Set FSO = New FileSystemObject
   
    Set SourceFolder = FSO.GetFolder(SourceFolderFullName)
   
    For Each FileItem In SourceFolder.Files
      ' 'Debug.Print FileItem.Name
       If FSO.GetExtensionName(FileItem.Name) = FileExtension Or FileExtension = "All" Then
         Debug.Print FileItem.Name
         col.Add (FileItem.Name)
       End If
       
    Next FileItem
   
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set GetMatchingFiles = col
End Function
Public Sub testit()
  Dim path As String
  Dim col As Collection
  Dim i As Integer

  path = CurrentProject.path
  Set col = GetMatchingFiles(path, "TIFF")
  Debug.Print "There are "; col.Count; " Files.)"
  For i = 1 To col.Count
    Debug.Print col(i)
  Next i
End Sub

Obviously that could be sped up by removing the collection and only returning the count. However, still requires going file by file.

Do you need to check subfolders?

 

[CedarTree]

Thanks. I was TRYING to avoid going file by file since the # of folders is HUGE. Oh well.... thanks all!

 

[MajP]

Have you done any tests by looping. I am curious if this is relatively slow or fast in FSO.

 

[CedarTree]

Yeah - it's not Access heavy - it's FSO heavy. So it depends on server speed, etc. It's alright - we're talking days, not weeks.

 

[MajP]

I do not know all that you are doing with the counting, but you may consider doing this outside of the Access application. If done in something like .Net you can use the IO.directory. I have not tried it, but the syntax is:
Dim MyFiles1() As String = IO.Directory.GetFiles("c:\", "*.txt")

 

[ebs17]

The command line statement shown above can also be executed as VBScript, which would create a process that differs from Access.

 

[The_Doc_Man]

Your concern is speed plus a large number of files to identify. I'm going to suggest a hybrid solution since you are looking for that speed. I'm of the opinion that using a shell DIR command to create a file (that could subsequently be imported) would make this faster. The problem with an all-Access solution is that going through Access and VBA looping, it SHOULD be slow for a couple of reasons.

First, you are using an interface to the file system where part of the (hidden) code has to translate a file-primitive command to a VBA context, which is part the Component Object Model interface. Second, VBA code is interpreted (or emulated), not executed, which is a slower process. The more VBA you have, the slower this will get.

If you use the output of a DIR command as described earlier, DIR is hard-coded and executes quickly. Further, it is DESIGNED to diddle directly with the file system, so there is either no interface or less of an interface that has to convert from one format to another.

Then, if you use one of the minimalist output options from DIR (suppressing headers, trailers, and other properties), you should be able to directly import that list into a table. From there you could use an SQL statement to count matching records based on the string containing the trailing .TIFF file type. SQL is ALSO hard-coded in its operation so should be faster than a loop even though it would be dealing with a sub-set of the file-spec strings and sub-sets - particularly RIGHT( string, 5 ) or any RIGHT function totally precludes any other optimization.

 

[ebs17]

you should be able to directly import that list into a table

Based on my suggestion: The text file with one column can be directly linked or imported as a table.
In addition to the desired number, you also have information about paths and file names.