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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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 ? "....