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

116,682

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
Share:
116,682

Related videos on Youtube

user2151190
Author by

user2151190

Updated on May 26, 2020

Comments

  • user2151190
    user2151190 over 3 years

    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.

  • user2151190
    user2151190 over 10 years
    Hi Thanks a lot for your answer! But how can I set the cel Range ("Q8").value that it will be the cell where the count of folders will be displayed please? That is a bit my problem becouse I don't know how to SET it :-(
  • Santosh
    Santosh over 10 years
    @user2151190 i have updated the answer. You can assign Range("Q8") the value of count variable by using Range("Q8").Value = count.
  • user2151190
    user2151190 over 10 years
    Thanks, I tried just a little bit wrong! YOU ARE THE BEST!!! Thanks a lot Santosh!!!!
  • user2151190
    user2151190 over 10 years
    Can I make it dynamic with a sreenupdating Application.ScreenUpdating = True ? Just finding the way to immediate see the right amount of folders without have to launch each time the code.
  • Santosh
    Santosh over 10 years
    Application.ScreenUpdating = True makes the code run faster and avoids screen flickering. Refer this link for [details] (msdn.microsoft.com/en-us/library/office/ff193498.aspx)
  • user2151190
    user2151190 over 10 years
    I have to set it on False and the code will run like a spear! But is there a way for immediate counting files when any changes of amount of files are made please? Is there a way to let screenupdating diplay every change of amount of foles without having to launch every time the code again please?
  • user2151190
    user2151190 over 10 years
    ok, problem solved, I called this code into a former code! Thanks a lot for the advise!!!!!!!!!!
  • Rocketq
    Rocketq over 8 years
    @Santosh Why sometimes Dir() can return same filename twice?
  • Shai Alon
    Shai Alon over 6 years

Related