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
Related videos on Youtube
Author by
user2151190
Updated on May 26, 2020Comments
-
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 over 10 yearsHi 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 over 10 years@user2151190 i have updated the answer. You can assign
Range("Q8")
the value ofcount
variable by usingRange("Q8").Value = count
. -
user2151190 over 10 yearsThanks, I tried just a little bit wrong! YOU ARE THE BEST!!! Thanks a lot Santosh!!!!
-
user2151190 over 10 yearsCan 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 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 over 10 yearsI 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 over 10 yearsok, problem solved, I called this code into a former code! Thanks a lot for the advise!!!!!!!!!!
-
Rocketq over 8 years@Santosh Why sometimes Dir() can return same filename twice?
-
Shai Alon over 6 yearsPossible explanation: stackoverflow.com/questions/37318717/…