count files in specific folder and display the number into 1 cel

file vba xls
user2151190 picture user2151190 · May 25, 2013 · Viewed 108.5k times · Source

I am trying to know how many files there are in 1 specific folder with .xls extension. I read hundreds of examples with message boxes, but that is not what I'm looking for. I just want to have the number displayed into 1 cel.

Is there someone who likes to help me with that please? I can not post any attempts because I can not get started :-(

best regards, E.

Answer

Santosh picture Santosh · May 26, 2013

Try below code :

Assign the path of the folder to variable FolderPath before running the below code.

Sub sample()

    Dim FolderPath As String, path As String, count As Integer
    FolderPath = "C:\Documents and Settings\Santosh\Desktop"

    path = FolderPath & "\*.xls"

    Filename = Dir(path)

    Do While Filename <> ""
       count = count + 1
        Filename = Dir()
    Loop

    Range("Q8").Value = count
    'MsgBox count & " : files found in folder"
End Sub

Related questions

VBA check if file exists

I have this code. It is supposed to check if a file exists and open it if it does. It does work if the file exists, and if it doesn't, however, whenever I leave the textbox blank and click the …

Read More
How do I Replace a String in a Line of a Text File Using FileSystemObject in VBA?

I am trying to use FileSystemObject methods to find a specific line in a text file, and within that line replace a specific string. I am relatively new to this, as my current code has excel open the text file …

Read More
Using VBA FileSystemObject, specific file File extension

I'm using the following code to list all files with xls,xlsx or xlsm extension from folder and its subfolder. The following code works but the problem is, it lists all files with all extensions from subfolders but lists only …

Read More