8

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
  • 189
  • 3
  • 4
  • 16

1 Answers1

23

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
Santosh
  • 12,175
  • 4
  • 41
  • 72
  • 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 :-( – user2151190 May 26 '13 at 06:47
  • @user2151190 i have updated the answer. You can assign `Range("Q8")` the value of `count` variable by using `Range("Q8").Value = count`. – Santosh May 26 '13 at 06:52
  • Thanks, I tried just a little bit wrong! YOU ARE THE BEST!!! Thanks a lot Santosh!!!! – user2151190 May 26 '13 at 06:58
  • 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. – user2151190 May 26 '13 at 07:03
  • `Application.ScreenUpdating = True` makes the code run faster and avoids screen flickering. Refer this link for [details] (http://msdn.microsoft.com/en-us/library/office/ff193498.aspx) – Santosh May 26 '13 at 07:08
  • 1
    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 May 26 '13 at 07:28
  • ok, problem solved, I called this code into a former code! Thanks a lot for the advise!!!!!!!!!! – user2151190 May 26 '13 at 08:17
  • @Santosh Why sometimes Dir() can return same filename twice? – Rocketq Jul 13 '15 at 15:52
  • Possible explanation: http://stackoverflow.com/questions/37318717/vba-subsequent-calls-to-dir-returns-same-file – Shai Alon May 07 '17 at 20:09