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

kylehink

New Member
Joined
Mar 8, 2019
Messages
1
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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,788
Office Version
  1. 365
Platform
  1. Windows
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

Code:
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
 
Last edited:

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,432
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:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,541
Messages
5,529,436
Members
409,877
Latest member
DDhol
Top