How can I count files in folder-CodePudding

I would like to count the files in folder with Excel VBA.

The speed very important for me now, so first I list all files (from folders and subfolders) to "A" column, and I would like to go thru on all lines and count how many files in the folder.

My list from "A" column:

D:\Steam\libraryfolder.vdf    
D:\Steam\steam.dll    
D:\Steam\config\appconfig.json    
D:\Steam\config\chaperone_info.vrchap    
D:\Steam\config\steamvr.vrsettings    
D:\Steam\config\lighthouse\lighthousedb.json    
D:\Steam\config\lighthouse\lhr-eebe0f79\config.json    
D:\Steam\config\lighthouse\lhr-eebe0f79\userdata\Green_46GA163X002581_mura_analyzes.mc    
D:\Steam\config\lighthouse\lhr-eebe0f79\userdata\Green_46HA163P000228_mura_analyzes.mc

I would like to get to the "B" column the number of the files. So the "B" column should looks like this:

At the moment I have this small code, to count the "", but I dont know unfortunately how I can count the files.

Sub test()

Dim S As String

S = "D:\Steam\config\lighthouse\lhr-eebe0f79\config.json"

MsgBox "count = " & UBound(Split(S, "\"))

End Sub

CodePudding user response：

You don't need VBA for this, standard Excel functions can compute these counts.

Column B is used to extract the filename from the path:

=MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))) 1,LEN(A1))

Column C is then used to extract the path:

=LEFT(A1,LEN(A1)-LEN(B1))

Finally, Column D can count the number of files that reside in the same directory:

=COUNTIF(C:C,$C1)

If you really do need to do this in VBA, then here's a couple of functions that will extract the filename or directory given a full path:

' Returns the file name given a full file path
Function BaseName(FilePath)
    BaseName = Mid(FilePath, InStrRev(FilePath, "\")   1)
End Function

' Returns the directory path given a full file path
Function DirName(FilePath)
    DirName = Mid(FilePath, 1, Len(FilePath) - Len(BaseName(FilePath)))
End Function