VBA that counts files in all subfolder of todays date folder, then return count to a H11

[kylehink]

I need a script that will count all files ending in "_1" in all subfolder, then return that number to cell "H11" by pressing a key or by a certain time interval.

It needs to select todays folder with this format 20190308-F3-kh, only paying attention to the 20190308 as the date. The "-F3-kh" isn't important.

The path would be /Volumes/ShootTo-F3/Capture-<wbr>F3/20190308-F3-kh/Capture. then count the files ending in "_1" in all subfolders.

After Days of searching I've only been able to find one piece of code that counted files in a folder but couldn't even get that to work after tweaking to my specifics. It was also written for a mac but I didn't really think that would matter.

Any help would be FANTASTIC.

 

[Yongle]

Here is something to get you started
Place the VBA in a new workbook
It creates in a list (in active sheet) of ALL file names ending _1 in all subfolders of Fldr (including Name, Folder and Count)

Amend to ignore the unwanted subfolders
You may want to reset the count for each folder
etc

Sub CountFiles()
    Dim FSO As Object, Fldr As String
    Fldr = "C:\myFolder\mySubfolder"
    Set FSO = CreateObject("Scripting.FileSystemObject")
    NextFolder FSO.GetFolder(Fldr)
End Sub
Private Sub NextFolder(Folder)
    Dim SubFolder, ch As String, aCount As Long, cel As Range
    For Each SubFolder In Folder.SubFolders
        NextFolder SubFolder
    Next
    Dim File
    For Each File In Folder.Files
        ch = Mid(File, InStrRev(File, ".") - 2, 2)
        On Error Resume Next
        If ch = "_1" Then
            aCount = aCount + 1
            Set cel = Cells(Rows.Count, 1).End(xlUp).Offset(1)
            cel = File
            cel.Offset(, 1) = Folder
            cel.Offset(, 2) = aCount
        End If
    Next
End Sub

 

[John_w]

It needs to select todays folder with this format 20190308-F3-kh, only paying attention to the 20190308 as the date. The "-F3-kh" isn't important.

The path would be /Volumes/ShootTo-F3/Capture-<wbr>F3/20190308-F3-kh/Capture. then count the files ending in "_1" in all subfolders.

Is that path correct? It doesn't specify a drive letter and folders are usually separated by back slashes, not forward slashes.

Try this macro, which specifies your path exactly as given:

Public Sub Count_Files()
     
    Dim fileSpec As String
    Dim tempFile As String
    Dim psCommand As Variant
    Dim numFiles As Long
     
    fileSpec = "/Volumes/ShootTo-F3/Capture-F3/" & Format(Date, "YYYYMMDD") & "-F3-kh/Capture/*_1.*"
         
    tempFile = Environ("temp") & "\temp.txt"
    psCommand = "PowerShell -Command " & Q("Get-ChildItem -File '" & fileSpec & "' -Recurse | Measure-Object | %{$_.Count} | Out-File -filePath '" & tempFile & "' -encoding ASCII")
    
    CreateObject("WScript.Shell").Run psCommand, 0, True
    numFiles = CreateObject("Scripting.FileSystemObject").OpenTextFile(tempFile).ReadAll
    Kill tempFile
    
    Range("H11").Value = numFiles
    
End Sub