I need a file count returned to a cell

  • Thread starter Thread starter Oldetowne
  • Start date Start date
O

Oldetowne

My sheet contains an entry for a folder. I need to return the file count from
that directory into another cell in the same row. I am only moderately
familiar with using macros and scripting. Any help will be very much
appreciated
 
Hi,

Maybe something like this

Sub LoopThroughDirectory()
Application.DisplayAlerts = False

MyPath = Sheets("Sheet1").Range("A1")
ActiveFile = Dir(MyPath & "*.xls")
Do While ActiveFile <> ""
Count = Count + 1
ActiveFile = Dir()
Loop
Application.DisplayAlerts = True
Sheets("Sheet1").Range("B1") = Count
End Sub

Mike
 
Perhaps better as a function

call with

=filecount(range contaning path,filter)

=filecount(a1,"xls")

or

=filecount(a1,"doc")



Function filecount(MyPath As String, filter As String) As Long
Application.DisplayAlerts = False
MyPath = Sheets("Sheet1").Range("A1")
ActiveFile = dir(MyPath & "*." & filter)
Do While ActiveFile <> ""
Count = Count + 1
ActiveFile = dir()
Loop
Application.DisplayAlerts = True
filecount = Count
End Function

Mike
 
Another way. Probably does NOT work in 2007
Sub countfiles()
With Application.FileSearch
.LookIn = "C:\a"
.FileType = msoFileTypeExcelWorkbooks
MsgBox (.FoundFiles.Count)
End With
End Sub
 
In Excel 2010 (Tech Preview) I get error 445 "Object does not support this
action" pointing to first statement in sub. So it seems your are right, Don,
about it not running in XL2007
best wishes
 
This function will return the count of the number of files in the directory
path passed into it...
 
This function will return the count of the number of files in the directory
path passed into it...

Function FileCount(Path As String) As Long
On Error Resume Next
FileCount = -1
FileCount = CreateObject("Scripting.FileSystemObject"). _
GetFolder(Path).Files.Count
End Function

So, as an example, you would call it like this...

MsgBox FileCount("c:\temp")

If the folder path passed into the function doesn't exist, then the function
returns a count of -1 (minus one).
 
Back
Top