Count files in a folder

Hey folks,

I am not an expert in VBA, but according to my research, the function I intend to perform will not be possible to do with excel formulas. In this case I will have to dedicate myself to programming in VBA Macros.

I'm looking for a function that tells me the number of files in a specific folder, and put the result in a specific cell on the worksheet.

The exemple :

I want to know how many files i have in this folder :

C:\Users\Veiga\Desktop\Final\PR0015\

And i want the result ( number of files ) in a specific Cell ( D9 ) exemple .

That is the main task for second and better task i want to upgrade the code, the folder PR0015 have more similar folders like : PR0015; PR0016; PR0017; PR0018 ......


So i want to put the folder name in the Cell ( A1 ) exmple
Then i want to know how many files i have in this folder :

C:\Users\Veiga\Desktop\Final\ (A1) \

Like this is a dynamic worksheet where can i change the address

In last task it will be possible to make in the same cell (D9) a droplist with the name of all the files names inside the folder .

Thank you for your help,

Best regards ,

Veiga

http://www.vbaexpress.com/kb/getarticle.php?kb_id=238

works for me and gives the total number of files

Sub LoopThroughFiles()
 
Dim oFSO As Object
Dim foldername As String
Dim oFolder As Object
Dim oFile As Object
Dim i As Integer
foldername = Range("a1").Value


Set oFSO = CreateObject("Scripting.FileSystemObject")
 
Set oFolder = oFSO.GetFolder(foldername)
 i = 1
For Each oFile In oFolder.Files

    Cells(i + 1, 1) = oFile.Name
 
    i = i + 1
 
Next oFile
 
End Sub

Will give you a list of all the files in the folder in range A1, alter accordingly. This range would have to be set as the source for the dropdown you are wanting in the cell

Might also be able to work it into a UDF

So, if your path was in A1, you could put the formula =Splitfiles(A1) which would give you the number and the list of files, at least in O365.

Function SplitFiles(Filepath As String)
Dim fso As Object, objFile As Object
Dim aFiles As Variant
ReDim aFiles(0)
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(Filepath)
aFiles(0) = oFolder.Files.Count
For Each objFile In oFolder.Files
    ReDim Preserve aFiles(UBound(aFiles) + 1)
    aFiles(UBound(aFiles)) = objFile.Name
Next
SplitFiles = Application.Transpose(aFiles)
End Function

Hello Good Morning .

Thank you very much for your help . I used the code of --davsth-- , it was very very good .

--- davsth in your code we can add 2 more rules:

- Count the number of files and Subfolders inside a folder ( ex: Result = 8 -- 5 files and 3 subfolders )
- And if the the file doesn't exist - the result show me the text ( NOT FIND )

Again, thank you very much for your help.

Veiga

Does a file exist

https://www.automateexcel.com/vba/file-exists/

for the other question I would probably just count the files listed, if the folders are names without . countif(list of files and folders,"*.*") would give the number

Davsth, thanks ,

Just a small help again :

In the code :

Sub CheckFolderExists ()

Dim strFolderName As String
Dim strFolderExists As String

strFolderName = "C:\Users\Nikola\Desktop\VBA articles\Test Folder\"
strFolderExists = Dir(strFolderName, vbDirectory)

If strFolderExists = "" Then
MsgBox "The selected folder doesn't exist"
Else
MsgBox "The selected folder exists"
End If

End Sub

When i have this line -Else
MsgBox "The selected folder exists"

can i put the routine " ELSE - Counting files and folders inside that extention ? "....